2021/11/14

#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器

 大家好, 今天來和大家分享 “一起來學樹莓派”, 

本週的主題為: “#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器”.


上期的資料庫查詢的方式是固定時間軸的, 每次要查詢資料時, 可能都要重新下 SQL 指令.

本期要介紹的是使用 Slider 滑桿的方式來調整時間的區間, 並用 Interval 滑桿來調整時間的平均值, 使查詢資料的方法更便利. 


另外, 本次也介紹如何使用 MQTT out 來從遠端來開/關繼電器. 

(如果你要接上 110V or 220V 電器, 請自行小心高壓電的危險! 本人無法負責您的風險)


一起來學樹莓派吧!


這影片主要內容有:

  • 如何用 Slider 建立動態查詢 InfluxDB 的界面

  • 用 MQTT out 來遠端開/關繼電器 (可以接上電器開/關)

  • 如何把 SQL 讀出的資料解析送到 Dashboard Chart

  • 使用 Layout 工具來佈置 dashboard 排列位置

  • 使用 CSS template 來改變 dashboard 外觀


如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!




影片時間軸:

00:00 開始 00:34 課程開始 02:29 在 NodeRED 中開始寫程式 03:48 建立Dashboard (後來不小心被我刪除.. :( 05:27 將時間/平均時間 Sliders 加到立Dashboard 08:20 將變數儲存到 context.flow 10:45 解釋 SQL 命令的內容 11:50 從Flow 變數中取得 hours 變數內容 12:58 點擊Deploy儲存Nodes 14:29 使用 Influx client 在 terminal 上查看SQL的正確性 15:20 使用 Link in / Link out nodes 讓畫面更清楚 18:25 查看資料庫讀取後資料結構 20:33 使用 .toFixed(2) 設定小數點位數為 2 位 25:45 使用 Layout 工具來佈置 dashboard 排列位置 28:05 複製自其他 flow 的 Nodes 31:39 加入 Switch / MQTT out Nodes 34:39 解釋 ESP8266 + 繼電器程式碼 37:25 根據收到的 Message 來打開/關閉 relay 38:21 實機展示:用 MQTT來遠端打開/關閉繼電器 38:53 危險! 注意高壓電可能會致命或使人受到傷害!! 39:14 使用 Template (CSS) 來自行定義Dashboard 的外觀


Create query 程式碼(A):

msg.query = "select mean(temp) as temp, mean(humd) as humd from studioTemp where location='studio' and time > now() - ";
msg.query += flow.get("hours") + "h GROUP by time(" + flow.get("interval") + "m) FILL(0)";

return msg;

Format DB data 程式碼(B):

var My_series = ["Temperature °C", "Humidity %"];
var My_labels = ["temp", "Humd"];
var data = msg.payload;

// var data = msg.payload;
// Not sure the same query produced differnet results...?

temp = data.map(point=> {return {x:point.time , y:point.temp.toFixed(2)}});
humd = data.map(point=> {return {x:point.time , y:point.humd.toFixed(2)}});
var My_data = [temp, humd];

msg.payload = [{ series: My_series, data: My_data, labels: My_labels}]

return msg;

NodeRED source code is available here: Download it here.

Stonez56 一起來學樹莓派系列影片: 

參考資料:

2021/10/31

#6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示

大家好, 今天來和大家分享 “一起來學樹莓派, 寫入InfluxDB 資料庫”. 上集分享的是資料送到 MQTT 顯示到資訊看板後, 時間後後資料就消失了. 本集是把所有的資料收集起來之後, 可以用來以後做為資料分析的依據. 以前你可能要把資料寫到 Thinkspeak server上, 現在自己家裡的樹莓派上就可以做到了! 一起來學樹莓派吧!


這影片內容有:
  • 安裝 InfluxDB 資料庫
  • 比較 InfluxDB 與關聯式資庫的不同
  • 如何把ESP32送到 MQTT 資料寫入InfluxDB 資料庫
  • 讀取InfluxDB 資料庫, 再把這些資料在資訊看板上顯示出來




Format code for writing InfluxDB

// Create the object key to match DB filed name from topic name(studio/temp1)
msg.payload["temp"] = msg.payload["studio/temp1"];
// Delete the unwanted studio/temp1
delete msg.payload["studio/temp1"];
// Update temp from string to float
msg.payload["temp"] = parseFloat(msg.payload["temp"]);

msg.payload["humd"] = msg.payload["studio/humd1"];
delete msg.payload["studio/humd1"];
msg.payload["humd"] = parseFloat(msg.payload["humd"]);

//remove unused topic
delete msg.topic;

//Create a temporary array
var data = [];
data.push(msg.payload);


//Add object to DB Tag(index field)
data.push({"location":"studio"});
//msg.payload.push({"location":"studio"});

//Copy temporary array to msg.payload
msg.payload = data;
return msg;

Format code for reading historical InfluxDB data


//Source from: https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665 var series = ["Temperature °C", "Humdity %"]; var labels = ["temp", "Humdity"]; var data0 = "["; var thetime0; for (var i=0; i < msg.payload.results[0].series[0].values.length; i++) { thetime0 = (msg.payload.results[0].series[0].values[i][0]); // Some manipulation of the time may be required thetime0 = Date.parse(thetime0); data0 += '{ "x":' + thetime0 + ', "y":' + (msg.payload.results[0].series[0].values[i][1]) + '}'; if (i < (msg.payload.results[0].series[0].values.length - 1)) { data0 += "," } else { data0 += "]" } } var data1 = "["; var thetime1; for (var j=0; j < msg.payload.results[0].series[0].values.length; j++) { thetime1 = (msg.payload.results[0].series[0].values[j][0]); // Some manipulation of the time may be required thetime1 = Date.parse(thetime1); data1 += '{ "x":' + thetime1 + ', "y":' + (msg.payload.results[0].series[0].values[j][2]) + '}'; if (j < (msg.payload.results[0].series[0].values.length - 1)) { data1 += "," } else { data1 += "]" } } var data = [data0, data1]; var jsondata = [JSON.parse(data0), JSON.parse(data1)]; msg.payload = [{"series": series, "data": jsondata, "labels": labels}]; msg.dataPoints = data; return msg;

請在此下載本篇教學的 NodeRead 程式: #6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示

更新:
影片內容不小心略過這個 InfluxDB Out Node (Write to studioTempDB)的內容:

InfluxDB out node 內容有兩項資料要輸入:
(1) 定義 InfluxDB Server, 按下鉛筆符號(見下圖)
(2) 寫入的 Measurement 資料表名稱,我們的範例裡,Measurement = studioTemp

定義 InfluxDB Server:
(1) InfluxDB - 在 Flow 裡看到的名稱
(2) InfluxDB 版本 - 教學裡案裝的是 v1.64
(3) Host: InfluxDB 的主機: 由於我們裝在本機,所以用 : raspberrypi.local, Port: 8086
(4) 資料庫名稱: 範例裡使用 studioTemp
(5) 資料庫帳號及密碼: 如果未設定可略過


Stonez56 一起來學樹莓派系列影片: 


2021/10/23

#5 2021 一起學 Raspberry Pi 4 - 溫溼度感測器DHT11由 ESP32 經 MQTT 送到 Node-RED

大家好, 今天來和大家分享如何把 DHT11溫溼度感測器上的資料由ESP32 讀取, 並用MQTT Client 發佈到 Mosquitto Server(MQTT), 再送到 Node-RED上, 再把這些資料在資訊看板上顯示出來. 


本篇使用到的程式碼:

//---- Setup update frequency -----//
const long TEMP_updateInterval = 10000; // How long to change temp and update, 10000 = 10 sec
unsigned long TEMP_currentMillis = 0;
unsigned long TEMP_previousMillis = 0; // store last time temp update

#include "DHT.h"
#define DHTPIN 23  //ESP32 pin 23
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE); // Initialize DHT sensor

/*
  SimpleMQTTClient.ino
  The purpose of this exemple is to illustrate a simple handling of MQTT and Wifi connection.
  Once it connects successfully to a Wifi network and a MQTT broker, it subscribe to a topic and send a message to it.
  It will also send a message delayed 5 seconds later.
*/

#include "EspMQTTClient.h"

EspMQTTClient client(
    "Your_WiFi_AP",     // Wi-Fi AP name
    "Wi-FI_Password", // Wi-Fi Password
    "192.168.0.119", // MQTT Broker server ip
    "",                  // Broker user name; Can be omitted if not needed
    "",                  // Broker password; Can be omitted if not needed
    "ESP32_Client",      // Client name that uniquely identify your device
    1883                 // The MQTT port, default to 1883. this line can be omitted
);

void setup()
{
    Serial.begin(115200);
    //Start DH11 sensor
    dht.begin();
    
    // Optionnal functionnalities of EspMQTTClient :
    client.enableDebuggingMessages(); // Enable debugging messages sent to serial output
    //client.enableHTTPWebUpdater();  // Enable the web updater. User and password default to values of MQTTUsername and MQTTPassword. These can be overrited with enableHTTPWebUpdater("user", "password").
    //client.enableLastWillMessage("TestClient/lastwill", "I am going offline"); // You can activate the retain flag by setting the third parameter to true
}

// This function is called once everything is connected (Wifi and MQTT)
// WARNING : YOU MUST IMPLEMENT IT IF YOU USE EspMQTTClient
void onConnectionEstablished()
{
    // Subscribe to "mytopic/test" and display received message to Serial
    client.subscribe("message/hello", [](const String &payload)
                     { Serial.println(payload); });

    // Subscribe to "mytopic/wildcardtest/#" and display received message to Serial
    //client.subscribe("mytopic/wildcardtest/#", [](const String &topic, const String &payload)
    //                 { Serial.println("(From wildcard) topic: " + topic + ", payload: " + payload); });

    // Publish a message to "mytopic/test"
    client.publish("studio/temp1", "Let's go!"); // You can activate the retain flag by setting the third parameter to true

    // Execute delayed instructions
    //client.executeDelayed(5 * 1000, []()
    //                      { client.publish("mytopic/wildcardtest/test123", "This is a message sent 5 seconds later"); });
}



void loop()
{

    TEMP_currentMillis = millis();
    if(TEMP_currentMillis - TEMP_previousMillis >= TEMP_updateInterval){
        TEMP_previousMillis = TEMP_currentMillis;

        // Reading temperature or humidity takes about 250 milliseconds!
        // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
        float humidity = dht.readHumidity();
        float temperature = dht.readTemperature();
        // Read temperature as Fahrenheit (isFahrenheit = true)
        //float f = dht.readTemperature(true);

        // Check if any reads failed and exit early (to try again).
        if (isnan(humidity) || isnan(temperature))
        {
            Serial.println(F("Failed to read from DHT sensor!"));
            return;
        }

        String tmp = "Current Temperature: ";
        String hud = "Current Humidity: ";
        Serial.print(tmp);
        Serial.println(String(temperature));
        Serial.print(hud);
        Serial.println(String(humidity));
        //Publish
        client.publish("studio/temp1", String(temperature));
        client.publish("studio/humd1", String(humidity));

        // Here is how to subscribe
        //client.subscribe("message/hello", [](const String &payload) { Serial.println(payload); });
    }

    client.loop();
}

*** 影片中忘了說明, 在 VS Code 裡, 按下 F1 到 Arduino Library Manager 裡, 搜尋 EspMQTTClient V.11.1版本, 並按下安裝 , 程式才能正確執行喔~~ ***


如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸:

00:29 課程開始 00:52 如何將 DHT11 連接到 ESP32 (接線圖) 00:59 在 ESP32 利用 EspMQTTClient 來寫程式 01:22 安裝 DHT11 程式庫 02:16 設定 EspMQTTClient 02:45 由 Raspberry Pi Terminal 找出 Mosquitto(MQTT) IP 地址 04:51 不要用 delay() 在程式 Loop 裡! 06:50 上傳並檢查發佈/訂閱的 messages 07:54 安裝 Node-RED 資訊看板(在 Node-RED 中) 08:57 按主題來訂閱消息 09:58 設置 MQTT 服務器的位置 11:34 建立資訊看板選項 Tabs Links & Groups 17:00 建立歷史資料資訊看板 20:32 查看資訊看板 20:50 資訊看板中的選項 Tabs Links & Groups在哪裡

下一集將介紹如何把歷史資料存儲到樹莓派的資料庫(influxDBvor MySQL)中, 以取得長時間的記錄資料. 


Stonez56 一起來學樹莓派系列影片: 


參考資料:

EspMQTTClient: https://github.com/plapointe6/EspMQTTClient 

2021/09/04

VSCODE Arduino Can't select programmer

I've made a video tutorial on how to use Arduino + VSCODE for a better coding environment.

One of YouTube visitor asked why he can't select Arduino Programmer. 13:53 in the video
I did a bit experiment and found out that for ESP8266 & EPS32, you can't select Arduino Programmer. I'm not sure if this is a bug or Arduino extension intended. I then plugged a Arduino Nano, then the programmer shown correctly.

Here is how to solve the problem, if you are use ESP8266 or ESP32:
On the left hand side, click "arduino.json" to edit the file.

Add this line: "programmer":"arduino:avrisp", 

Example:
{
    "sketch": "ESP32-Robot-car\\ESP32-Robot-car.ino",
    "configuration": "CPUFreq=240,FlashMode=qio,FlashFreq=80",
    "board": "esp32:esp32:esp32cam",
    "port": "COM3",
    "programmer": "arduino:avrisp",
    "output": "../build"
}

Then this config the programmer for your boards. Hope this helped!

The End.



VSCODE + Arduino - 無法選擇 Programmer: AVISP

上方的VSCODE + Arduino 影片教學是之前的作的.  有網友詢問, 為何在 13:53 秒左右, 
影片中的 Select Programmer 時, 清單確是空的?

我後來試了發現 ESP8266/ESP32-S 真的不會出現 programmer list, 但是你改用 Nano, Mini Pro應該就會出現了. 

解決方法:
在左手邊的 arduino.json 自行加入 "programmer":"arduino:avrisp", 
範例:
{
    "sketch": "ESP32-Robot-car\\ESP32-Robot-car.ino",
    "configuration": "CPUFreq=240,FlashMode=qio,FlashFreq=80",
    "board": "esp32:esp32:esp32cam",
    "port": "COM3",
    "programmer": "arduino:avrisp",
    "output": "../build"
}

這樣子你就不用選擇 Arduino Programmer 了! 希望這樣能夠解決這個問題. 

2021/08/08

Arduino - How to use U8g2 library to show graphic & UTF8 characters for monochrome displays

In order to use the newly purchased 0.96" OLED SD1306 display, I studied the u8g2 library and made this video to share with you on how to use U8g2 library with monochrome display. In the video, I also included a section on how to use the free 5000+ traditional Chinese font created by Mr. Yang. The file is free for anyone to use. Really appreciated what he did for Arduino community! For those who do not have time to watch the entire video , here is the video timeline.

Video Timeline:

00:22 Install u8g2 01:04 Open PrintUTF8 example 02:00 Define display used 02:23 Explain display naming rules 04:13 Uncomment the line selected 04:33 Explain the code 04:42 enableUTF8Print() for symbol and Chinese 05:14 u8g2 supported font list 06:23 display location setCursor(X,Y) 07:05 Upload the sketch & show characters 07:23 Can't overwrite characters! 08:08 Char overwrite solution 08:37 OledOverlayPrint() function 09:06 Use OledOverlayPrint() in code 10:08 Use Open Iconic Fonts 11:29 Display cloud symbol 11:52 \u with number to show cloud 13:02 Change fonts for different lines 14:34 Download 5000+ Chinese font 15:11 Install 5000+Chinese font 15:39 Test missing fonts again 16:42 Reminder of memory usage 17:45 End of tutorial

References:

U8g2 library: https://github.com/olikraus/u8g2 U8g2 plain font: https://github.com/olikraus/u8g2/wiki... U8g2 setCursor command: https://github.com/olikraus/u8g2/wiki... U8g2 iconic font: https://github.com/olikraus/u8g2/wiki... How to make your own font: https://blog.jmaker.com.tw/chinese_oled/ MQTT & IoT Integration FB group: https://www.facebook.com/groups/36360... Download 5000+ Chinese font: https://drive.google.com/drive/folder...

 END

------------------------------------------------------------------------------------------------------------------------------------------------