NodeRed 搭建web服务器--⑤通过MQTT.fx向TDengine写入数据

已于 2022-03-08 11:06:33 修改
阅读量1.2k 收藏 7
点赞数 1
分类专栏： onenet Node-Red 文章标签： tdengine big data 大数据
于 2022-03-08 11:03:05 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/armcsdn/article/details/123347374
版权
本文档介绍了如何使用MQTT.fx软件将数据推送到TDengine数据库，并通过TDengineGUI展示数据。主要问题包括：NCHAR字段需用单引号括起，SQL语句不区分大小写。示例代码展示了数据提取、转换和写入的过程，涉及时间戳、字符串和数值类型的处理。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前面第四篇已经提取到HTTP数据推送的数据，本篇将把获取的数据写入到TDengine中。

需要MQTT.fx软件和TDengineGUI软件。MQTT.fx负责制造上传数据，TDengineGUI用于展现数据库收到的数据。

TDengineGUI下载链接

先上流图：

 由于前面4篇文章已经对相关技术要点进行了讲解，本篇只讲解写入数据库遇到的问题。

问题1：TDengine 写入NCHAR字段时字符串两端必须用单引号，双引号不可以。否者无法写入数据。

var time=msg.payload.time;
var deviceId=msg.payload.deviceId;
var deviceName="'"+msg.payload.deviceName+"'";  // 字符串两端使用单引号引用,详细说明见链接   
var Power="'"+msg.payload.Power+"'";//https://www.taosdata.com/docs/cn/v2.0/taos-sql#-4
var Temp=msg.payload.Temp;
const table='armxu_onenet_db.onenet_test'
msg.payload='insert into '+ table+' values ('+time +','+deviceId+','+deviceName+','+Power+','+Temp+');';
return msg;

 问题2：TDengine 对 SQL 语句中的英文字符不区分大小写，自动转化为小写执行。

#类型Bytes说明
1TIMESTAMP8时间戳。缺省精度毫秒，可支持微秒和纳秒。从格林威治时间 1970-01-01 00:00:00.000 (UTC/GMT) 开始，计时不能早于该时间。（从 2.0.18.0 版本开始，已经去除了这一时间范围限制）（从 2.1.5.0 版本开始支持纳秒精度）
2INT4整型，范围 [-2^31+1, 2^31-1], -2^31 用作 NULL
3BIGINT8长整型，范围 [-2^63+1, 2^63-1], -2^63 用于 NULL
4FLOAT4浮点型，有效位数 6-7，范围 [-3.4E38, 3.4E38]
5DOUBLE8双精度浮点型，有效位数 15-16，范围 [-1.7E308, 1.7E308]
6BINARY自定义记录单字节字符串，建议只用于处理 ASCII 可见字符，中文等多字节字符需使用 nchar。理论上，最长可以有 16374 字节。binary 仅支持字符串输入，字符串两端需使用单引号引用。使用时须指定大小，如 binary(20) 定义了最长为 20 个单字节字符的字符串，每个字符占 1 byte 的存储空间，总共固定占用 20 bytes 的空间，此时如果用户字符串超出 20 字节将会报错。对于字符串内的单引号，可以用转义字符反斜线加单引号来表示，即 \’。
7SMALLINT2短整型， 范围 [-32767, 32767], -32768 用于 NULL
8TINYINT1单字节整型，范围 [-127, 127], -128 用于 NULL
9BOOL1布尔型，{true, false}
10NCHAR自定义记录包含多字节字符在内的字符串，如中文字符。每个 nchar 字符占用 4 bytes 的存储空间。字符串两端使用单引号引用，字符串内的单引号需用转义字符 \’。nchar 使用时须指定字符串大小，类型为 nchar(10) 的列表示此列的字符串最多存储 10 个 nchar 字符，会固定占用 40 bytes 的空间。如果用户字符串长度超出声明长度，将会报错。
11JSONjson数据类型， 只有tag类型可以是json格式

 数据库常用语句，请根据自己需求更改名称。

create database atestdb;
show databases;
use atestdb;
show tables;
create table t (ts timestamp, counter int);
create table onenet_test (ts timestamp, deviceId int,deviceName NCHAR(20),Power NCHAR(20),Temp FLOAT);

发送数据用 MQTT.fx，下划横线分别代表2个不同的设备

上传数据格式

{
    "id": "123",
    "version": "1.0",
    "params": {
        "Power": {
            "value": "2435"           
        },
        "temp": {
            "value": 46.6           
        }   
    }
}

  

 成功写入数据库如图：

 完整代码如下(如需自己试验，请对域进行修改)：

[
    {
        "id": "0fd09364725a5015",
        "type": "tab",
        "label": "流程 7",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "cd7cdf3bb348f91f",
        "type": "http in",
        "z": "0fd09364725a5015",
        "name": "",
        "url": "/node",
        "method": "get",
        "upload": false,
        "swaggerDoc": "",
        "x": 120,
        "y": 200,
        "wires": [
            [
                "bf61b0882de84888",
                "7b433526daaac6ad"
            ]
        ]
    },
    {
        "id": "9e4f60d84798cc57",
        "type": "http response",
        "z": "0fd09364725a5015",
        "name": "",
        "statusCode": "",
        "headers": {},
        "x": 510,
        "y": 240,
        "wires": []
    },
    {
        "id": "bf61b0882de84888",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 350,
        "y": 180,
        "wires": []
    },
    {
        "id": "7b433526daaac6ad",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "",
        "func": "msg.payload=msg.payload.msg\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 330,
        "y": 240,
        "wires": [
            [
                "9e4f60d84798cc57"
            ]
        ]
    },
    {
        "id": "568c1710900830e6",
        "type": "http in",
        "z": "0fd09364725a5015",
        "name": "处理推送POST请求",
        "url": "/node",
        "method": "post",
        "upload": false,
        "swaggerDoc": "",
        "x": 150,
        "y": 340,
        "wires": [
            [
                "1e0aad73f38f3137",
                "b6cd5317107fca49",
                "ee7a64e90baa01a0"
            ]
        ]
    },
    {
        "id": "631328d69c736eef",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "Power数据",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 930,
        "y": 340,
        "wires": []
    },
    {
        "id": "1e0aad73f38f3137",
        "type": "http response",
        "z": "0fd09364725a5015",
        "name": "响应状态码",
        "statusCode": "200",
        "headers": {},
        "x": 370,
        "y": 380,
        "wires": []
    },
    {
        "id": "ee7a64e90baa01a0",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "提取msg",
        "func": "msg.payload=msg.payload.msg;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 360,
        "y": 340,
        "wires": [
            [
                "833a1f1bd58ba96b",
                "cd7b28862feb2dae"
            ]
        ]
    },
    {
        "id": "b6cd5317107fca49",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "原样",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 350,
        "y": 300,
        "wires": []
    },
    {
        "id": "833a1f1bd58ba96b",
        "type": "json",
        "z": "0fd09364725a5015",
        "name": "",
        "property": "payload",
        "action": "obj",
        "pretty": false,
        "x": 530,
        "y": 340,
        "wires": [
            [
                "35ad2bfd1a20c74f",
                "cc9414b7ebab6828",
                "058c8bf90313ec11",
                "cfba991849f79082",
                "3d39f1e721536ded"
            ]
        ]
    },
    {
        "id": "058c8bf90313ec11",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "提取Power",
        "func": "var newd = msg.payload.data.params.Power;\n//var newd =msg.payload.data.params.Power.value;\nmsg.payload=newd;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 710,
        "y": 340,
        "wires": [
            [
                "631328d69c736eef"
            ]
        ]
    },
    {
        "id": "cd7b28862feb2dae",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "提取1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 510,
        "y": 300,
        "wires": []
    },
    {
        "id": "cfba991849f79082",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "提取Temp",
        "func": "var newd = msg.payload.data.params.temp;\nmsg.payload=newd;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 380,
        "wires": [
            [
                "86b3fa5090a534d1"
            ]
        ]
    },
    {
        "id": "86b3fa5090a534d1",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "Temp数据",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 380,
        "wires": []
    },
    {
        "id": "4a6875fb5a9a2fe8",
        "type": "inject",
        "z": "0fd09364725a5015",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 150,
        "y": 660,
        "wires": [
            [
                "525ef895aa261944"
            ]
        ]
    },
    {
        "id": "525ef895aa261944",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "添加请求参数",
        "func": "msg.payload = \"show databases;\";\nmsg.headers = {};\nmsg.headers['Authorization'] = 'Basic cm9vdDp0YW9zZGF0YQ==';\nreturn msg;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 360,
        "y": 660,
        "wires": [
            [
                "f94293f8c3e3bbdd"
            ]
        ]
    },
    {
        "id": "f94293f8c3e3bbdd",
        "type": "http request",
        "z": "0fd09364725a5015",
        "name": "TDengine连接请求",
        "method": "POST",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "qqx.armxu.top:6041/rest/sql/armxu_onenet_db",
        "tls": "",
        "persist": true,
        "proxy": "",
        "authType": "",
        "senderr": false,
        "x": 610,
        "y": 660,
        "wires": [
            [
                "7642db831e2832d0"
            ]
        ]
    },
    {
        "id": "7642db831e2832d0",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 870,
        "y": 660,
        "wires": []
    },
    {
        "id": "3d39f1e721536ded",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "整合数据",
        "func": "var data={}\nvar m_deviceId = msg.payload.deviceId;\nvar m_deviceName = msg.payload.deviceName;\nvar m_temp = msg.payload.data.params.temp;\nvar m_Power = msg.payload.data.params.Power;\nvar Power=m_Power.value;\ndata.time=m_Power.time;\ndata.deviceId=m_deviceId;\ndata.deviceName=m_deviceName;\ndata.Power=m_Power.value;\ndata.Temp=m_temp.value;\nmsg.payload=data;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 420,
        "wires": [
            [
                "98974c5ccb99764a",
                "107f8b362cd9c3c6"
            ]
        ]
    },
    {
        "id": "98974c5ccb99764a",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "整合数据输出",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 420,
        "wires": []
    },
    {
        "id": "cc9414b7ebab6828",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "提取设备名称",
        "func": "var newd = msg.payload.deviceName;\nmsg.payload=newd;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 720,
        "y": 300,
        "wires": [
            [
                "a194684fe7ac89cb"
            ]
        ]
    },
    {
        "id": "a194684fe7ac89cb",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "deviceName输出",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 950,
        "y": 300,
        "wires": []
    },
    {
        "id": "35ad2bfd1a20c74f",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "提取DeviceId",
        "func": "var newd = msg.payload.deviceId;\nmsg.payload=newd;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 710,
        "y": 260,
        "wires": [
            [
                "33f987c56fa8706f"
            ]
        ]
    },
    {
        "id": "33f987c56fa8706f",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "deviceId数据",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 930,
        "y": 260,
        "wires": []
    },
    {
        "id": "5dce5fa64d9a269a",
        "type": "http request",
        "z": "0fd09364725a5015",
        "name": "TDengine连接请求",
        "method": "POST",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "qqx.armxu.top:66041/rest/sql/armxu_onenet_db",
        "tls": "",
        "persist": true,
        "proxy": "",
        "authType": "",
        "senderr": false,
        "x": 1030,
        "y": 500,
        "wires": [
            [
                "6bbc405efadddb33"
            ]
        ]
    },
    {
        "id": "107f8b362cd9c3c6",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "生成插入数据库语句",
        "func": "var time=msg.payload.time;\nvar deviceId=msg.payload.deviceId;\nvar deviceName=\"'\"+msg.payload.deviceName+\"'\";  // 字符串两端使用单引号引用,详细说明见链接   \nvar Power=\"'\"+msg.payload.Power+\"'\";//https://www.taosdata.com/docs/cn/v2.0/taos-sql#-4\nvar Temp=msg.payload.Temp;\nconst table='armxu_onenet_db.onenet_test'\nmsg.payload='insert into '+ table+' values ('+time +','+deviceId+','+deviceName+','+Power+','+Temp+');';\nreturn msg;",
        "outputs": 2,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 580,
        "y": 500,
        "wires": [
            [
                "4bedb69cca1a7123"
            ],
            [
                "58ed6a0adf377f4b"
            ]
        ]
    },
    {
        "id": "4bedb69cca1a7123",
        "type": "function",
        "z": "0fd09364725a5015",
        "name": "向数据库表中写入数据",
        "func": "msg.payload=msg.payload;\nmsg.headers = {};\nmsg.headers['Authorization'] = 'Basic cm9vdDp0YW9zZGF0YQ==';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 820,
        "y": 500,
        "wires": [
            [
                "5dce5fa64d9a269a"
            ]
        ]
    },
    {
        "id": "6bbc405efadddb33",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "数据输出",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1200,
        "y": 500,
        "wires": []
    },
    {
        "id": "58ed6a0adf377f4b",
        "type": "debug",
        "z": "0fd09364725a5015",
        "name": "插入语句输出",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 820,
        "y": 540,
        "wires": []
    },
    {
        "id": "6ecb9371ca7882d8",
        "type": "comment",
        "z": "0fd09364725a5015",
        "name": "数据库的连接测试",
        "info": "### **下面流图用于数据库的连接测试**",
        "x": 170,
        "y": 620,
        "wires": []
    }
]

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

armcsdn

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值