devbox
繁依Fanyi0
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：繁依Fanyi0 | 2024-03-10 13:26:20

赞

踩

mqtt写入tdengine

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

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

TDengineGUI下载链接

先上流图：

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

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

  1. var time=msg.payload.time;
  2. var deviceId=msg.payload.deviceId;
  3. var deviceName="'"+msg.payload.deviceName+"'";  // 字符串两端使用单引号引用,详细说明见链接   
  4. var Power="'"+msg.payload.Power+"'";//https://www.taosdata.com/docs/cn/v2.0/taos-sql#-4
  5. var Temp=msg.payload.Temp;
  6. const table='armxu_onenet_db.onenet_test'
  7. msg.payload='insert into '+ table+' values ('+time +','+deviceId+','+deviceName+','+Power+','+Temp+');';
  8. 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格式

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

  1. create database atestdb;
  2. show databases;
  3. use atestdb;
  4. show tables;
  5. create table t (ts timestamp, counter int);
  6. create table onenet_test (ts timestamp, deviceId int,deviceName NCHAR(20),Power NCHAR(20),Temp FLOAT);

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

上传数据格式

  1. {
  2.     "id": "123",
  3.     "version": "1.0",
  4.     "params": {
  5.         "Power": {
  6.             "value": "2435"           
  7.         },
  8.         "temp": {
  9.             "value": 46.6           
  10.         }   
  11.     }
  12. }

  

 成功写入数据库如图：

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

  1. [
  2.     {
  3.         "id": "0fd09364725a5015",
  4.         "type": "tab",
  5.         "label": "流程 7",
  6.         "disabled": false,
  7.         "info": "",
  8.         "env": []
  9.     },
  10.     {
  11.         "id": "cd7cdf3bb348f91f",
  12.         "type": "http in",
  13.         "z": "0fd09364725a5015",
  14.         "name": "",
  15.         "url": "/node",
  16.         "method": "get",
  17.         "upload": false,
  18.         "swaggerDoc": "",
  19.         "x": 120,
  20.         "y": 200,
  21.         "wires": [
  22.             [
  23.                 "bf61b0882de84888",
  24.                 "7b433526daaac6ad"
  25.             ]
  26.         ]
  27.     },
  28.     {
  29.         "id": "9e4f60d84798cc57",
  30.         "type": "http response",
  31.         "z": "0fd09364725a5015",
  32.         "name": "",
  33.         "statusCode": "",
  34.         "headers": {},
  35.         "x": 510,
  36.         "y": 240,
  37.         "wires": []
  38.     },
  39.     {
  40.         "id": "bf61b0882de84888",
  41.         "type": "debug",
  42.         "z": "0fd09364725a5015",
  43.         "name": "",
  44.         "active": true,
  45.         "tosidebar": true,
  46.         "console": false,
  47.         "tostatus": false,
  48.         "complete": "false",
  49.         "statusVal": "",
  50.         "statusType": "auto",
  51.         "x": 350,
  52.         "y": 180,
  53.         "wires": []
  54.     },
  55.     {
  56.         "id": "7b433526daaac6ad",
  57.         "type": "function",
  58.         "z": "0fd09364725a5015",
  59.         "name": "",
  60.         "func": "msg.payload=msg.payload.msg\nreturn msg;",
  61.         "outputs": 1,
  62.         "noerr": 0,
  63.         "initialize": "",
  64.         "finalize": "",
  65.         "libs": [],
  66.         "x": 330,
  67.         "y": 240,
  68.         "wires": [
  69.             [
  70.                 "9e4f60d84798cc57"
  71.             ]
  72.         ]
  73.     },
  74.     {
  75.         "id": "568c1710900830e6",
  76.         "type": "http in",
  77.         "z": "0fd09364725a5015",
  78.         "name": "处理推送POST请求",
  79.         "url": "/node",
  80.         "method": "post",
  81.         "upload": false,
  82.         "swaggerDoc": "",
  83.         "x": 150,
  84.         "y": 340,
  85.         "wires": [
  86.             [
  87.                 "1e0aad73f38f3137",
  88.                 "b6cd5317107fca49",
  89.                 "ee7a64e90baa01a0"
  90.             ]
  91.         ]
  92.     },
  93.     {
  94.         "id": "631328d69c736eef",
  95.         "type": "debug",
  96.         "z": "0fd09364725a5015",
  97.         "name": "Power数据",
  98.         "active": true,
  99.         "tosidebar": true,
  100.         "console": false,
  101.         "tostatus": false,
  102.         "complete": "payload",
  103.         "targetType": "msg",
  104.         "statusVal": "",
  105.         "statusType": "auto",
  106.         "x": 930,
  107.         "y": 340,
  108.         "wires": []
  109.     },
  110.     {
  111.         "id": "1e0aad73f38f3137",
  112.         "type": "http response",
  113.         "z": "0fd09364725a5015",
  114.         "name": "响应状态码",
  115.         "statusCode": "200",
  116.         "headers": {},
  117.         "x": 370,
  118.         "y": 380,
  119.         "wires": []
  120.     },
  121.     {
  122.         "id": "ee7a64e90baa01a0",
  123.         "type": "function",
  124.         "z": "0fd09364725a5015",
  125.         "name": "提取msg",
  126.         "func": "msg.payload=msg.payload.msg;\nreturn msg;",
  127.         "outputs": 1,
  128.         "noerr": 0,
  129.         "initialize": "",
  130.         "finalize": "",
  131.         "libs": [],
  132.         "x": 360,
  133.         "y": 340,
  134.         "wires": [
  135.             [
  136.                 "833a1f1bd58ba96b",
  137.                 "cd7b28862feb2dae"
  138.             ]
  139.         ]
  140.     },
  141.     {
  142.         "id": "b6cd5317107fca49",
  143.         "type": "debug",
  144.         "z": "0fd09364725a5015",
  145.         "name": "原样",
  146.         "active": true,
  147.         "tosidebar": true,
  148.         "console": false,
  149.         "tostatus": false,
  150.         "complete": "payload",
  151.         "targetType": "msg",
  152.         "statusVal": "",
  153.         "statusType": "auto",
  154.         "x": 350,
  155.         "y": 300,
  156.         "wires": []
  157.     },
  158.     {
  159.         "id": "833a1f1bd58ba96b",
  160.         "type": "json",
  161.         "z": "0fd09364725a5015",
  162.         "name": "",
  163.         "property": "payload",
  164.         "action": "obj",
  165.         "pretty": false,
  166.         "x": 530,
  167.         "y": 340,
  168.         "wires": [
  169.             [
  170.                 "35ad2bfd1a20c74f",
  171.                 "cc9414b7ebab6828",
  172.                 "058c8bf90313ec11",
  173.                 "cfba991849f79082",
  174.                 "3d39f1e721536ded"
  175.             ]
  176.         ]
  177.     },
  178.     {
  179.         "id": "058c8bf90313ec11",
  180.         "type": "function",
  181.         "z": "0fd09364725a5015",
  182.         "name": "提取Power",
  183.         "func": "var newd = msg.payload.data.params.Power;\n//var newd =msg.payload.data.params.Power.value;\nmsg.payload=newd;\nreturn msg;",
  184.         "outputs": 1,
  185.         "noerr": 0,
  186.         "initialize": "",
  187.         "finalize": "",
  188.         "libs": [],
  189.         "x": 710,
  190.         "y": 340,
  191.         "wires": [
  192.             [
  193.                 "631328d69c736eef"
  194.             ]
  195.         ]
  196.     },
  197.     {
  198.         "id": "cd7b28862feb2dae",
  199.         "type": "debug",
  200.         "z": "0fd09364725a5015",
  201.         "name": "提取1",
  202.         "active": true,
  203.         "tosidebar": true,
  204.         "console": false,
  205.         "tostatus": false,
  206.         "complete": "payload",
  207.         "targetType": "msg",
  208.         "statusVal": "",
  209.         "statusType": "auto",
  210.         "x": 510,
  211.         "y": 300,
  212.         "wires": []
  213.     },
  214.     {
  215.         "id": "cfba991849f79082",
  216.         "type": "function",
  217.         "z": "0fd09364725a5015",
  218.         "name": "提取Temp",
  219.         "func": "var newd = msg.payload.data.params.temp;\nmsg.payload=newd;\nreturn msg;",
  220.         "outputs": 1,
  221.         "noerr": 0,
  222.         "initialize": "",
  223.         "finalize": "",
  224.         "libs": [],
  225.         "x": 700,
  226.         "y": 380,
  227.         "wires": [
  228.             [
  229.                 "86b3fa5090a534d1"
  230.             ]
  231.         ]
  232.     },
  233.     {
  234.         "id": "86b3fa5090a534d1",
  235.         "type": "debug",
  236.         "z": "0fd09364725a5015",
  237.         "name": "Temp数据",
  238.         "active": true,
  239.         "tosidebar": true,
  240.         "console": false,
  241.         "tostatus": false,
  242.         "complete": "payload",
  243.         "targetType": "msg",
  244.         "statusVal": "",
  245.         "statusType": "auto",
  246.         "x": 920,
  247.         "y": 380,
  248.         "wires": []
  249.     },
  250.     {
  251.         "id": "4a6875fb5a9a2fe8",
  252.         "type": "inject",
  253.         "z": "0fd09364725a5015",
  254.         "name": "",
  255.         "props": [
  256.             {
  257.                 "p": "payload"
  258.             },
  259.             {
  260.                 "p": "topic",
  261.                 "vt": "str"
  262.             }
  263.         ],
  264.         "repeat": "",
  265.         "crontab": "",
  266.         "once": false,
  267.         "onceDelay": 0.1,
  268.         "topic": "",
  269.         "payload": "",
  270.         "payloadType": "date",
  271.         "x": 150,
  272.         "y": 660,
  273.         "wires": [
  274.             [
  275.                 "525ef895aa261944"
  276.             ]
  277.         ]
  278.     },
  279.     {
  280.         "id": "525ef895aa261944",
  281.         "type": "function",
  282.         "z": "0fd09364725a5015",
  283.         "name": "添加请求参数",
  284.         "func": "msg.payload = \"show databases;\";\nmsg.headers = {};\nmsg.headers['Authorization'] = 'Basic cm9vdDp0YW9zZGF0YQ==';\nreturn msg;\n",
  285.         "outputs": 1,
  286.         "noerr": 0,
  287.         "initialize": "",
  288.         "finalize": "",
  289.         "libs": [],
  290.         "x": 360,
  291.         "y": 660,
  292.         "wires": [
  293.             [
  294.                 "f94293f8c3e3bbdd"
  295.             ]
  296.         ]
  297.     },
  298.     {
  299.         "id": "f94293f8c3e3bbdd",
  300.         "type": "http request",
  301.         "z": "0fd09364725a5015",
  302.         "name": "TDengine连接请求",
  303.         "method": "POST",
  304.         "ret": "txt",
  305.         "paytoqs": "ignore",
  306.         "url": "qqx.armxu.top:6041/rest/sql/armxu_onenet_db",
  307.         "tls": "",
  308.         "persist": true,
  309.         "proxy": "",
  310.         "authType": "",
  311.         "senderr": false,
  312.         "x": 610,
  313.         "y": 660,
  314.         "wires": [
  315.             [
  316.                 "7642db831e2832d0"
  317.             ]
  318.         ]
  319.     },
  320.     {
  321.         "id": "7642db831e2832d0",
  322.         "type": "debug",
  323.         "z": "0fd09364725a5015",
  324.         "name": "",
  325.         "active": true,
  326.         "tosidebar": true,
  327.         "console": false,
  328.         "tostatus": false,
  329.         "complete": "false",
  330.         "statusVal": "",
  331.         "statusType": "auto",
  332.         "x": 870,
  333.         "y": 660,
  334.         "wires": []
  335.     },
  336.     {
  337.         "id": "3d39f1e721536ded",
  338.         "type": "function",
  339.         "z": "0fd09364725a5015",
  340.         "name": "整合数据",
  341.         "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;",
  342.         "outputs": 1,
  343.         "noerr": 0,
  344.         "initialize": "",
  345.         "finalize": "",
  346.         "libs": [],
  347.         "x": 700,
  348.         "y": 420,
  349.         "wires": [
  350.             [
  351.                 "98974c5ccb99764a",
  352.                 "107f8b362cd9c3c6"
  353.             ]
  354.         ]
  355.     },
  356.     {
  357.         "id": "98974c5ccb99764a",
  358.         "type": "debug",
  359.         "z": "0fd09364725a5015",
  360.         "name": "整合数据输出",
  361.         "active": true,
  362.         "tosidebar": true,
  363.         "console": false,
  364.         "tostatus": false,
  365.         "complete": "payload",
  366.         "targetType": "msg",
  367.         "statusVal": "",
  368.         "statusType": "auto",
  369.         "x": 920,
  370.         "y": 420,
  371.         "wires": []
  372.     },
  373.     {
  374.         "id": "cc9414b7ebab6828",
  375.         "type": "function",
  376.         "z": "0fd09364725a5015",
  377.         "name": "提取设备名称",
  378.         "func": "var newd = msg.payload.deviceName;\nmsg.payload=newd;\nreturn msg;",
  379.         "outputs": 1,
  380.         "noerr": 0,
  381.         "initialize": "",
  382.         "finalize": "",
  383.         "libs": [],
  384.         "x": 720,
  385.         "y": 300,
  386.         "wires": [
  387.             [
  388.                 "a194684fe7ac89cb"
  389.             ]
  390.         ]
  391.     },
  392.     {
  393.         "id": "a194684fe7ac89cb",
  394.         "type": "debug",
  395.         "z": "0fd09364725a5015",
  396.         "name": "deviceName输出",
  397.         "active": true,
  398.         "tosidebar": true,
  399.         "console": false,
  400.         "tostatus": false,
  401.         "complete": "payload",
  402.         "targetType": "msg",
  403.         "statusVal": "",
  404.         "statusType": "auto",
  405.         "x": 950,
  406.         "y": 300,
  407.         "wires": []
  408.     },
  409.     {
  410.         "id": "35ad2bfd1a20c74f",
  411.         "type": "function",
  412.         "z": "0fd09364725a5015",
  413.         "name": "提取DeviceId",
  414.         "func": "var newd = msg.payload.deviceId;\nmsg.payload=newd;\nreturn msg;",
  415.         "outputs": 1,
  416.         "noerr": 0,
  417.         "initialize": "",
  418.         "finalize": "",
  419.         "libs": [],
  420.         "x": 710,
  421.         "y": 260,
  422.         "wires": [
  423.             [
  424.                 "33f987c56fa8706f"
  425.             ]
  426.         ]
  427.     },
  428.     {
  429.         "id": "33f987c56fa8706f",
  430.         "type": "debug",
  431.         "z": "0fd09364725a5015",
  432.         "name": "deviceId数据",
  433.         "active": true,
  434.         "tosidebar": true,
  435.         "console": false,
  436.         "tostatus": false,
  437.         "complete": "payload",
  438.         "targetType": "msg",
  439.         "statusVal": "",
  440.         "statusType": "auto",
  441.         "x": 930,
  442.         "y": 260,
  443.         "wires": []
  444.     },
  445.     {
  446.         "id": "5dce5fa64d9a269a",
  447.         "type": "http request",
  448.         "z": "0fd09364725a5015",
  449.         "name": "TDengine连接请求",
  450.         "method": "POST",
  451.         "ret": "txt",
  452.         "paytoqs": "ignore",
  453.         "url": "qqx.armxu.top:66041/rest/sql/armxu_onenet_db",
  454.         "tls": "",
  455.         "persist": true,
  456.         "proxy": "",
  457.         "authType": "",
  458.         "senderr": false,
  459.         "x": 1030,
  460.         "y": 500,
  461.         "wires": [
  462.             [
  463.                 "6bbc405efadddb33"
  464.             ]
  465.         ]
  466.     },
  467.     {
  468.         "id": "107f8b362cd9c3c6",
  469.         "type": "function",
  470.         "z": "0fd09364725a5015",
  471.         "name": "生成插入数据库语句",
  472.         "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;",
  473.         "outputs": 2,
  474.         "noerr": 0,
  475.         "initialize": "",
  476.         "finalize": "",
  477.         "libs": [],
  478.         "x": 580,
  479.         "y": 500,
  480.         "wires": [
  481.             [
  482.                 "4bedb69cca1a7123"
  483.             ],
  484.             [
  485.                 "58ed6a0adf377f4b"
  486.             ]
  487.         ]
  488.     },
  489.     {
  490.         "id": "4bedb69cca1a7123",
  491.         "type": "function",
  492.         "z": "0fd09364725a5015",
  493.         "name": "向数据库表中写入数据",
  494.         "func": "msg.payload=msg.payload;\nmsg.headers = {};\nmsg.headers['Authorization'] = 'Basic cm9vdDp0YW9zZGF0YQ==';\nreturn msg;",
  495.         "outputs": 1,
  496.         "noerr": 0,
  497.         "initialize": "",
  498.         "finalize": "",
  499.         "libs": [],
  500.         "x": 820,
  501.         "y": 500,
  502.         "wires": [
  503.             [
  504.                 "5dce5fa64d9a269a"
  505.             ]
  506.         ]
  507.     },
  508.     {
  509.         "id": "6bbc405efadddb33",
  510.         "type": "debug",
  511.         "z": "0fd09364725a5015",
  512.         "name": "数据输出",
  513.         "active": true,
  514.         "tosidebar": true,
  515.         "console": false,
  516.         "tostatus": false,
  517.         "complete": "payload",
  518.         "targetType": "msg",
  519.         "statusVal": "",
  520.         "statusType": "auto",
  521.         "x": 1200,
  522.         "y": 500,
  523.         "wires": []
  524.     },
  525.     {
  526.         "id": "58ed6a0adf377f4b",
  527.         "type": "debug",
  528.         "z": "0fd09364725a5015",
  529.         "name": "插入语句输出",
  530.         "active": true,
  531.         "tosidebar": true,
  532.         "console": false,
  533.         "tostatus": false,
  534.         "complete": "payload",
  535.         "targetType": "msg",
  536.         "statusVal": "",
  537.         "statusType": "auto",
  538.         "x": 820,
  539.         "y": 540,
  540.         "wires": []
  541.     },
  542.     {
  543.         "id": "6ecb9371ca7882d8",
  544.         "type": "comment",
  545.         "z": "0fd09364725a5015",
  546.         "name": "数据库的连接测试",
  547.         "info": "### **下面流图用于数据库的连接测试**",
  548.         "x": 170,
  549.         "y": 620,
  550.         "wires": []
  551.     }
  552. ]

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/218071
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号