Node-Red ile SQLite üzerinden veri analizi

tarih:

 

Bu bölümdeki anlatılanların görsel videosunu da izleyebilirsiniz:

Önceki bölümlerde, internet üzerinden aldığımız hava durumu verilerini, Node-Red üzerinde görselleştirip, SQLite veri tabanımıza kaydetmiştik.


Bu bölümde, önceki bölümde hazırladığımız ve bizim için bir kaç gündür veri toplayıp kaydeden Raspberry Pi cihazımızda toplanan veriyi görselleştireceğiz. Bunun için chart nodumuzu kullanarak üç tür grafik çizdireceğiz:
  1. Veriyi anlık olarak güncelleyen ve gösteren grafik
  2. Son 24 saatlik verinin, veri tabanından alınıp gösterildiği grafik
  3. Son bir haftanın en düşük, ortalama ve en yüksek sıcaklıklarının gösterildiği grafik



Önceki bölümde oluşturduğumuz flowun üzerine yapacağımız bu ekleme ile sıcaklık verisinin dashboard tarafında bir “chart” nodu ile grafik olarak gösterilmesini amaçlıyoruz. Verimizi çizdirmeden önce oluşturduğumuz SQLite veri tabanımızdan verinin nasıl çağırılacağını görmemiz gereklidir. Kullandığımız “chart” nodu, veriyi üç şekilde alabilmektedir. Bu ayrıntıyı, geliştiricilerin ilgili github sayfasında bulabilirsiniz:

https://github.com/node-red/node-red-dashboard/blob/master/Charts.md

Bu verilen açıklamaya göre, chart nodunu kullanmak için 3 adet yöntem vardır.

  1. Veriyi her seferinde “msg.payload” içerisinde inject etmek. Bunu birinci grafiğimizde kullanacağız.
  2. Tek bir veri dizisini x ve y eksenlerindeki değerler olarak vermek. Bunu ikinci grafiğimizde kullanacağız.
  3. Çoklu veri dizilerini çoklu grafikte göstermek. Bunu üçüncü grafiğimizde kullanacağız.

Verinin anlık olarak çizdirilmesi

Verinin anlık olarak çizdirilmesi için, grafiği çizilecek verinin, "msg.payload" öğesinde chart nodumuza verilmesi yeterlidir. Ancak openweathermap nodumuzun çıkışındaki verinin payload içeriği oldukça zengindi. sadece sıcaklık verisini içermesi için bu verinin yeniden oluşturulması gereklidir. Bunun için openweathermap nodumuzun sonuna bir function nodu ekliyoruz. bu nodumuzun içine aşağıdaki kodu yazıyoruz ve bunun çıkışını bir debug nodu ile gözlemliyoruz. 

var newMsg = {};
newMsg.payload = msg.payload.tempc;
return newMsg;


İşlemi tetiklediğimiz zaman, debug nodumuzdan aşağıdaki gibi bir veriyi almamız gereklidir:

msg.payload = 25.1


Nod kütüphanemizdeki dashboard grubundaki chart nodunu ekrana sürükleyip, yeni yazdığımız fonksiyonun çıkışına bağlıyoruz. Gerekli ayarlamaları yaptıktan sonra, dashboard ekranımızda ekteki gibi yeni veri geldikçe grafik çizmesi gerekmektedir.




Ancak, bu grafik, tarayıcı değiştiğinde veya uzun süre kullanılmadığı zaman, kendini sıfırlayıp, baştan bu veriyi kaydedip gösterecektir. Bu yüzden sonraki yöntemi önermekteyim.

Son 24 saatlik verinin kayıttan alınarak çizdirilmesi

Bu ve sonraki yöntemde ise verinin sqlite veri tabanından çekilip chart noduna iletilmesi kullanılacaktır.

İkinci maddede verilen türümüzde, tek bir veri dizisini grafik olarak çizdirmek için aşağıdaki gibi bir “msg.payload” objesi oluşturmamız gerekmektedir. Bunun tarifi yine ilgili github sayfasında verilmiştir. 

[{
	“series”:	[“graphic label”],
	“data”: 	[[
			{“x”:epochtime, “y”:value},
			{“x”:epochtime, “y”:value},
			{“x”:epochtime, “y”:value},
			....
			....
			{“x”:epochtime, “y”:value},
			{“x”:epochtime, “y”:value}
			]],
	“labels”:	[""]
}]

Bu karışık javascript nesnesi (javascript object) aslında sadece tekli değil çoklu zamana bağlı değişkenlerin gösterimi için kullanılabilmektedir. Çizgi tipi grafik çizdirmek için, bu tip bir payload verisi gereklidir. Bu şekilde formatlamamız gereken verimizi çekmek için aşağıdaki gibi bir SQL query yazarak ilk veri çekme adımımızı gerçekleştireceğiz: 

SELECT timestamp, sicaklik 
FROM havaDurumu 
WHERE timestamp>= datetime('now', '-24 HOUR');

Bu query sonucunu terminal üzerinden aşağıdaki gibi alabilmekteyiz.

Bu şekilde bir query almak ve bu query sonucunu Node-Red üzerinde görmek için aşağıdaki gibi nodlarımızı oluşturup bağlıyoruz:

Bu nodlarımızdan sqlite ve debug nodunu ayarlıyoruz. “inject” nodumuzu her zamanki gibi değiştirmiyoruz. “Function” nodumuza, terminalde deneyip istediğimiz sonucu aldığımız sorguyu (query) aşağıdaki gibi yazıyoruz: 

var newMsg = {};

newMsg.topic = “SELECT timestamp, sicaklik ”;
newMsg.topic += “FROM havaDurumu ”;
newMsg.topic += “WHERE timestamp>=datetime(‘now’, ‘-24 HOUR’);”;
return newMsg;



Deploy düğmesine basıp programımızı yükledikten sonra, “inject” nodumuz ile manuel işlemi tetikleyince aşağıdaki gibi bir sonuç elde ediyoruz:


Elde ettiğimiz veri aslında tam istediğimiz gibi değil. Bu verinin “chart” nodu tarafından kullanılabilmesi için öncelikle gelen verideki text öğelerinin sayıya dönüştürülmesi ve tarih saat formatının EPOCH olarak değiştirilmesi gereklidir. EPOCH formatı, 1970 yılından itibaren geçen süreyi saniye olarak kullanan ve UTC, yani Greenwich saatini kullanan bir sistemidir. Ayrıca, “ui_chart” nodumuz EPOCH formatını milisaniye cinsinden görmek istemektedir. Bunun için sorgumuzu aşağıdaki gibi değiştirip fonksiyon nodumuza ekliyoruz. Çektiğimiz verideki değer zaten UTC zaman bölgesine göre gelmektedir. 

SELECT 
strftime(‘%s’, timestamp)*1000 ‘x’, 
sicaklik ‘y’ 
FROM havaDurumu 
WHERE timestamp>= datetime(‘now’, ‘-24 HOUR’);

Bu sorguyu da fonksiyon nodunda aşağıdaki gibi değiştiriyoruz.

var newMsg = {};
newMsg.topic = “SELECT ”;
newMsg.topic += “strftime(‘%s’, timestamp)*1000 ‘x’, ”;
newMsg.topic += “sicaklik ‘y’ ”; 
newMsg.topic += “FROM havaDurumu ”;
newMsg.topic += “WHERE timestamp>= datetime(‘now’, ‘-24 HOUR’);”;
return newMsg;

Bu şekilde yapılan değişiklik sonrası, elde ettiğimiz debug ekranı sonucu aşağıdaki gibidir:

Şu anda veriler bizim istediğimiz formatta ama verinin tamamı istediğimiz formatta değil. Bunu “chart” nodunun kullanacağı şekilde “parse” edebilmemiz için “sqlite” nodunun sonuna bir fonksiyon daha koyup, bu fonksiyonun da çıkışını eklediğimiz chart nodumuza bağlayacağız.


“sqlite” nodumuzun çıkışındaki “function” nodumuzun içerisine aşağıdaki kodu yazıyoruz: 

var newMsg = {};
newMsg.payload = 	[{
			“series”:[“Sıcaklık”],
  			“data”:[msg.payload],
 			"labels”:[“”]
			}];

return newMsg;

Chart nodumuzun ayarlarını da aşağıdaki gibi yapıyoruz. Burada dikkat ettiğiniz üzere, x ekseni yani zaman eksenimizi saat ve dakika olarak ayarlıyoruz. Verimiz UTC gelse de, görüntülediğiz web tarayıcı ve bilgisayarda, bilgisayarın kullandığı zaman bölgesine göre otomatik olarak gösterecektir:


Deploy düğmesine basarak programımızı yüklüyoruz. Inject noduna basınca “dashboard” ekranında aşağıdaki gibi grafiğin çizilmiş olması gereklidir:


En son olarak bu grafiğin güncellenmesi için her data yazıldığı zaman bu işlemin yapılabilmesi amacıyla “inject” nodumuzu silip ilk “function” nodumuzu resimdeki gibi bağlıyoruz. Artık grafiğimiz her veri eklendiği zaman otomatik olarak güncellenecektir.


Son bir haftanın analizi

Yapacağımız bir analiz olan sıcaklık değişimlerinin günlük analizini grafik üzerinde göstererek “chart” nodunun son kullanım tipini de göreceğiz. Bu kullanım tipi daha çok zaman bazlı değil, kategori bazlı gösterimler için gereklidir. Örneğin günlük ortalamaların, haftalık ortalamaların, aylık değişimlerin gösterilmesi veya benzer verilerin karşılaştırılması gibi. Ayrıca bu gösterimde, önceki gösterimden farklı olarak bütün veriler ortak x ekseni verilerini kullanır. Yani vereceğimiz veri aşağıdaki gibi bir tablo olarak gösterilebilir. 

X Ekseni etiketi	Seri1	Seri2	Seri3
x[0]			y1[0]	y2[0]	y3[0]
x[1]			y1[1]	y2[1]	y3[1]
...			...	...	...
...			...	...	...
...			...	...	...
...			...	...	...

Önceki türde yaptığımız benzer adımları burada da yapacağız. Ancak önceki türden farklı olarak, bu sefer kullanacağımız verinin x ve y gibi öğeleri olmayacaktır. Github sayfasında gösterildiği üzere bu “msg.payload” verisinin şekli aşağıdaki gibi olmalıdır: 

[{
	“series”:[“seri1”, “seri2”, “seri3”],
	“data”:[[seri1 verileri], [seri2 verileri], [seri3 verileri]],
	“labels”:[“x ekseni noktası”, “x ekseni noktası”, “x ekseni noktası”]
}]

Gördüğümüz üzere, üç adet veri serisini bir “array” yani matris olarak yazmamız gerekmektedir. Bunu Excel üzerinde alt alta bir sütun olarak düşünebiliriz. Bu sonucu almak için yapacağımız SQL sorgusu sonucunda aşağıdaki gibi bir sonucun çıkmasını istiyoruz: 

dataDate	dataMin		dataAvg		dataMax
01-10-2021	15.0		22.0		23.0
02-10-2021	15.2		22.3		23.5
…		…		…		…
…		…		…		…
…		…		…		…

Bu sonucun ortaya çıkması için ise aşağıdaki gibi bir SQL komutunu yazmamız gerekmektedir: 

SELECT 
strftime(‘%d-%m-%Y’, timestamp, 'localtime') dataDate, 
min(sicaklik) dataMin, 
round(avg(sicaklik),1) dataAvg, 
max(sicaklik) dataMax 
FROM havaDurumu 
WHERE timestamp>=datetime(‘now’,’localtime’, ‘-7 DAY’, ‘start of day’) 
GROUP BY dataDate;


Bu SQL komutunu, SQL query olarak SQLite noduna göndermemiz için “msg.topic” öğesine string olarak yazmamız gerekmektedir. Bunu yapmanın iki yolu vardır: function noduna bu query stringini yazmak veya change noduna query stringi yazmak. Function nodu içerisindeki kod şu şekilde olmalıdır: 

var newMsg = {};
newMsg.topic = “SELECT ”;
newMsg.topic += “strftime(‘%d-%m-%Y’, timestamp, 'localtime') dataDate,”; 
newMsg.topic += “min(sicaklik) dataMin, ”;
newMsg.topic += “round(avg(sicaklik),1) dataAvg, 
newMsg.topic += “max(sicaklik) dataMax, ”;
newMsg.topic += “min(timestamp) startDate, ”; 
newMsg.topic += “max(timestamp) endDate ”;
newMsg.topic += “FROM havaDurumu ”;
newMsg.topic += “WHERE timestamp>=datetime(‘now’,’localtime’, ‘-7 DAY’, ‘start of day’) ”;
newMsg.topic += “GROUP BY dataDate;”;
return newMsg;

Alternatif olarak aşağıdaki kod yeni satıra geçmeden "change" noduna resimdeki gibi eklenebilir: 

SELECT strftime(‘%d-%m-%Y’, timestamp, 'localtime') dataDate, min(sicaklik) dataMin, round(avg(sicaklik),1) dataAvg, max(sicaklik) dataMax, min(timestamp) startDate, max(timestamp) endDate FROM havaDurumu WHERE timestamp>=datetime(‘now’,’localtime’, ‘-7 DAY’, ‘start of day’) GROUP BY dataDate;


Bu veriyi almak ve gelen veriyi görüntülemek için aşağıdaki gibi nodlarımızı flowa ekleyip bağlıyoruz. “Inject” nodundan gelen tetikleme sonucunda “msg.topic” içerisinde bulunan bu komut ile “sqlite” nodumuzdan gelen yanıt debug nodunda aşağıdaki gibi gözükmektedir.



Elde ettiğimiz veri istediğimize yakın formattadır ancak “ui_chart” nodunda bu veriyi görselleştirebilmemiz için bu veriyi biraz daha şekillendirmemiz gerekmektedir. Bu verinin nasıl olacağı konusunda ilgili github sayfasında ayrıntılı tanımlama yapılmıştır.

Buradaki açıklamaya göre verimizin aşağıdaki gibi olması gereklidir: 

[{
    "series": ["veri1 ismi", "veri2 ismi", "veri3 ismi" ],
    "data": [ [veri1 data array], [veri1 data array], [veri1 data array] ],
    "labels": [ "ortak x ekseni noktası1", "ortak x ekseni noktası 2", "ortak x ekseni noktası 3", "ortak x ekseni noktası 4" ]
}]

Bu veriyi verilen tarifteki gibi “parse” edebilmemiz için bir “function” nodu daha yazmamız ve “sqlite” nodunun çıkışına bağlamamız gerekmektedir. Bu fonksiyon nodunun çıkışını da ekleyeceğimiz "chart" noduna bağlıyoruz.


Eklediğimiz “function” nodunun içerisine aşağıdaki kodu yazarak chart nodunun kullanabileceği bir formata getiriyoruz: 

var newMsg = {};
var dataDate = [];
var dataMin = [];
var dataAvg = [];
var dataMax = [];

// verinin uzunluğundan bağımsız olarak bu işlemi yapması için döngünün uzunluğunu, msg.payload öğesinin array olarak büyüklüğü olarak ayarlayabiliyoruz.
for (var i=0; i

Sıra chart nodumuzun ayarlanmasına geldi. Burada yine bir “line chart” oluşturacağız ancak istersek “bar chart” da oluşturabiliriz.




Bu yaptığımız işlemler sonucunda, işlemimizi denemeye geçiyoruz. "inject" nodumuzu tetikleyerek, dahsboard üzerinde grafiğimizi elde ediyoruz.



En son olarak, analizin, her veri güncellendiği zaman tekrarlanması için "inject" nodumuzu kaldırıp, bağlandığı nodu önceki grafiğimizin çıkışına bağlıyoruz.

Sonuç

Bu bölümümüzde, SQLite veri tabanımızda kayıt ettiğimiz verileri görüntüleme ve bu verilerin SQL komutları ile temel analiz verisini elde ederek görüntüleme üzerine bir örnek yaptık. Buna benzer örnekler için takip etmeyi unutmayın.

Yorumlar

Yorum Gönder