Data acquisition of the Internet of Things involves the access of a large number of devices and the transmission of massive time series data. The combined technology stack of EMQ X message middleware and TDengine big data platform is fully capable of the transmission, storage and calculation of massive time series monitoring data in scenarios.

After data is stored in the database, other methods, such as data visualization system, are often needed to calculate and present the data according to the rules, so as to realize data monitoring, indicator statistics and other business requirements, so as to give full play to the value of data. TDengine and Grafana, an open source software, can quickly build a data visualization platform for the Internet of Things.

The above package solution does not require code development, and the products involved can all provide different levels of delivery modes of open source software, enterprise services and cloud SaaS services. The free version or enterprise version can be privatized and deployed in the cloud according to project requirements.

Plan to introduce

Introduction of EMQ X

EMQ X is an open source MQTT messaging server based on the highly concurrent Erlang/OTP language platform that supports mega connections and a distributed cluster architecture with a published-subscribe pattern. EMQ X has a number of features built in out of the box, including open source EMQ X Broker and Enterprise EMQ X Enterprise support for storing device messages to the TDengine through the rules engine.

What is TDengine

TDengine is a big data platform designed and optimized for Internet of Things, Internet of Vehicles, industrial Internet, IT operation and maintenance, etc. In addition to the core time series database function which is more than 10 times faster, it also provides caching, data subscription, streaming computing and other functions to minimize the complexity of research and development and operation and maintenance, and all the core code, including the cluster function, are open source.

TDengine provides community edition, Enterprise edition, and Cloud Service edition. See the TDengine usage documentation for the installation/use tutorial.

Grafana profile

Grafana is a cross-platform, open source metrics analysis and visualization tool that can query and process data from various data sources for visual presentation. It can quickly and flexibly create client-side charts, panel plug-ins have many different ways of visualized indicators and logs, the official library has rich dashboard plug-ins, such as heat maps, line charts, charts and other display; Supports data sources such as Graphite, TDengine, InfluxDB, OpenTSDB, Prometheus, Elasticsearch, CloudWatch and KairosDB, and supports independent/mixed query and display of data items; You can create custom alarm rules and notify them to other message processing services or components.

The business scenario

This paper simulates the environment data collection scenario of the Internet of Things. It is assumed that there are certain environmental data collection points, and all the data of the collection points are transmitted to the collection platform (MQTT Publish) through MQTT protocol. The theme design is as follows:

sensor/data
Copy the code

The data sent by the sensor is in JSON format. The data includes the temperature, humidity, noise volume, PM10, PM2.5, SULFUR dioxide, nitrogen dioxide, carbon monoxide, sensor ID, area, and collection time.

{" temperature ": 30," humidity ": 20," volume ": 44.5," PM10 ": 23," pm25: "61," SO2 ": 14," NO2 ": 4," CO ": 5," id ": "10-c6-1f-1a-1f-47", "area": 1, "ts": 1596157444170 }Copy the code

Now you need real-time storage so that you can view the data at any later time, making the following requirements:

  • Each device reports data at the frequency of every 5 seconds, and the database stores each piece of data for subsequent retrospective analysis.
  • You can use the visualization system to view the indicator data, such as the average value, maximum value, and minimum value, in any region and in any time period.

Environment to prepare

All components used in this paper have Docker images. Except for EMQ X, which needs to modify a few configuration to facilitate operation, download and installation, TDengine and Grafana are built with Docker.

Refer to their official websites for installation package resources and tutorials:

  • EMQ X: EMQ website
  • TDengine: the official website of TAos data
  • Grafana: website of Grafana

Install EMQ X

If you are new to EMQ X, it is recommended that you use the EMQ X documentation to get started quickly

Visit the EMQ official website to download the installation package suitable for your operating system. At the time of writing, the latest version of EMQ X open source version is V4.1.2. Download the zip package to start the following steps:

Unzip emqx-macosx-v4.1.1.zip CD emqx ## Start EMQ X in console mode./bin/emqx consoleCopy the code

After the system is successfully started, access http://127.0.0.1:18083 to access the Dashboard of the EMQ X administrative console, and use the default user name and password adminPublic to log in for the first time.

Install TDengine

In order to facilitate the test, installation can be performed by using Docker (network port mapping is required), or installation package can be used:

Docker run -d --name tdengine -p 6030-6041:6030-6041 tdengine/tdengine:latestCopy the code

Grafana installation

Install and start Grafana from Docker using the following command:

docker run -d --name=grafana -p 3000:3000 grafana/grafana
Copy the code

Log in to the Grafana visual panel using the default user name and password of Adminadmin. After login, change the password as prompted. Use the new password to log in to the main screen.

Configure EMQ X to store data to the TDengine

TDengine creates databases and tables

Enter the TDengine Docker container:

docker exec -it tdengine bash
Copy the code

Create database ‘test’ :

taos
create database test;
Copy the code

Create sensor_data table. See TAOS SQL for TDengine data structure and SQL commands:

use test;
CREATE TABLE sensor_data (
  ts timestamp,
   temperature float,
  humidity float,
  volume float,
  PM10 float,
  pm25 float,
  SO2 float,
  NO2 float,
  CO float,
  sensor_id NCHAR(255), 
  area TINYINT,
  coll_time timestamp
);
Copy the code

Configure the EMQ X rule engine

Open EMQ X Dashboared, go to the Rules Engine -> Rules page, click the Create button to go to the create page.

Rules of SQL

Rule SQL is used to filter EMQ X messages and events. The following SQL is used to filter payload data from the sensor/data subject:

SELECT
  payload
FROM
  "sensor/data"
Copy the code

Use SQL test function, input test data for filtering results test, test results and output as follows, indicating that THE SQL is written correctly:

{ "payload": 30, "{\" temperature \ ": \" humidity \ ": 20, \" volume \ ": 44.5, \" PM10 \ ": 23, \" pm2.5 \ ": 61, \" SO2 \ ": 14, \" NO2 \ ": 4, \" CO \ ": 5, \" id \ ": \" 10 - c6-1f-1a-1f-47\",\"area\":1,\"ts\":1596157444170}" }Copy the code

In response to the action

To support the development of different types of platforms, TDengine provides apis that conform to REST design standards. The simplest way to connect through a RESTful Connector is to use an HTTP request to carry authentication information and the SQL operation to perform the TDengine.

You can write data to the TDengine via the RESTful Connector using EMQ X open source version send to Web service. The upcoming EMQ X Enterprise version 4.1.1 will provide native higher-performance write Connectors.

Sending to the Web service requires two pieces of data, an association resource and a message content template.

  • Associated resource: HTTP server configuration information. Here is the RESTful Connector of the TDengine

  • Note that we should specify the database name in the SQL, and the character type should also be enclosed in single quotes. The message content template is:

    INSERT INTO test.sensor_data VALUES( now, payload.temperature,{payload.temperature}, payload.temperature,{payload.humidity}, payload.volume,{payload.volume}, payload.volume,{payload.PM10}, payload.pm25,{payload.pm25}, payload.pm25,{payload.SO2}, payload.NO2,{payload.NO2}, payload.NO2,{payload.CO}, ‘payload-id ‘, {payload-id}’, payload-id ‘, {payload-area}, ${payload-ts})

The creation process

Click the add button under the response action, select send data to the Web service in the popup box, and click New Resource to create a WebHook resource.

Choose Webhook resource type, fill in the request URL http://127.0.0.1:6041/rest/sql, select the POST request method, still need to add the Authorization request header as authentication information.

The Authorization value is a base64-encoded string of Basic + TDengine {username}:{password}. For example, root: taosData the encoded value is cm9vdDp0YW9zZGF0YQ==. The actual value is Basic cm9vdDp0YW9zZGF0YQ==

On the action creation page, select the newly created resource and fill in the message template content.

Generate simulation data

The following script simulates a scenario in which 10,000 devices report simulated data to EMQ X every five seconds for the past 24 hours.

  • Total data: 24 * 3600/5 * 10000 = 172 million
  • Message TPS: 2000

After installing Node.js and modifying the configuration parameters as required, you can start by running the following command:

npm install mqtt mockjs --save --registry=https://registry.npm.taobao.org
node mock.js
Copy the code

Note: The simulation generates data and sends it to the EMQ X code. Please adjust the parameters according to the cluster performance

// mock.js const mqtt = require('mqtt') const Mock = require('mockjs') const EMQX_SERVER = 'mqtt://localhost:1883' const CLIENT_NUM = 10000 const STEP = 10000 // Const AWAIT = 10000 // Ms const CLIENT_POOL = [] startMock() function sleep(timer = 100) {return new Promise(resolve => { setTimeout(resolve, timer) }) } async function startMock() { const now = Date.now() for (let i = 0; i < CLIENT_NUM; i++) { const client = await createClient(`mock_client_${i}`) CLIENT_POOL.push(client) } // last 24h every 5s const last = 24 * 3600 * 1000 for (let ts = now - last; ts <= now; ts += STEP) { for (const client of CLIENT_POOL) { const mockData = generateMockData() const data = { ... mockData, id: client.clientId, area: 0, ts, } client.publish('sensor/data', JSON.stringify(data)) } const dateStr = new Date(ts).toLocaleTimeString() console.log(`${dateStr} send success.`) await sleep(AWAIT) } console.log(`Done, use ${(Date.now() - now) / 1000}s`) } /** * Init a virtual mqtt client * @param {string} clientId ClientID */ function createClient(clientId) { return new Promise((resolve, reject) => { const client = mqtt.connect(EMQX_SERVER, { clientId, }) client.on('connect', () => { console.log(`client ${clientId} connected`) resolve(client) }) client.on('reconnect', () => { console.log('reconnect') }) client.on('error', (e) => { console.error(e) reject(e) }) }) } /** * Generate mock data */ function generateMockData() { return { "temperature": parseFloat(Mock.Random.float(22, 100).toFixed(2)), "humidity": parseFloat(Mock.Random.float(12, 86).toFixed(2)), "volume": parseFloat(Mock.Random.float(20, 200).toFixed(2)), "PM10": parseFloat(Mock.Random.float(0, 300).toFixed(2)), "pm25": parseFloat(Mock.Random.float(0, 300).toFixed(2)), "SO2": parseFloat(Mock.Random.float(0, 50).toFixed(2)), "NO2": parseFloat(Mock.Random.float(0, 50).toFixed(2)), "CO": parseFloat(Mock.Random.float(0, 50).toFixed(2)), "area": Mock.Random.integer(0, 20), "ts": 1596157444170, } }Copy the code

Visual configuration

After the components are installed and simulated data is successfully written, configure the data visualized on the Grafana visual interface.

Add data source

Add the data source, that is, the data source information displayed. Select a data source of the TDengine type and set connection parameters. By default, the key configuration information is as follows:

Adding a New Dashboard

After the data source is added, add the data dashboard information to be displayed. The Dashboard is a collection of visual panels. Click New Dashboard and select + Query to add data panels by querying.

Creating the panel requires four steps: Queries, Visualization, General, Alert, and creation time

Mean panel

Use Grafana’s visual query build tool to find the average of all devices.

The following SQL queries the average value of key indicators in the data based on the specified period (formform formto) and the specified interval ($interval) :

select avg(temperature), avg(humidity), avg(volume), avg(PM10), avg(pm25), avg(SO2), avg(NO2), avg(CO)  from test.sensor_data where coll_time >= $from and coll_time < $to interval($interval)
Copy the code

Visualization is not changed by default. Change the panel name to historical average in General. If you need to monitor alarms for services, you can choreograph alarm rules in Alert.

When you’re done, click the back button in the upper left corner to successfully add a data panel to the Dashboard. Click the top navigation bar to save the icon, and enter the Dashboard name to complete the creation of the Dashboard.

Maximum and minimum panels

Continue to click on Dashboard’s Add Panel button to Add the maximum and minimum charts. The operation procedure is the same as adding the average value. Only the SELECT statistical method field in the query can be adjusted to AVG function MAX and MIN:

select max(temperature), max(humidity), max(volume), max(PM10), max(pm25), max(SO2), max(NO2), max(CO), min(temperature), min(humidity), min(volume), min(PM10), min(pm25), min(SO2), min(NO2), min(CO)  from test.sensor_data where coll_time >= $from and coll_time < $to interval($interval)
Copy the code

Dashboard effect

Save the dashboard, and drag and drop each data panel to size and position it to get a visually good data dashboard. In the upper right corner of the dashboard, you can select the time interval and automatic refresh time. When the device continuously sends data collection data, the data value on the dashboard changes, achieving better visualization effect.

conclusion

So far, we have completed the system construction of the whole process of data transmission, storage and presentation of the Internet of Things with the help of EMQ X + TDengine. Readers can understand the application of EMQ X’s rich expansion capabilities and TDengine’s complete big data platform features in the data collection of the Internet of Things. Learning more about Grafana’s other features allows users to customize better data visualization and even monitoring and alerting systems.