Series
Part 1 - dotnet vs python vs node - temperature emission - who is cooler?
Part 2 - Developing modules
Part 3 - Custom Containers using Apache Nifi
Part 4 - Custom Module using TimescaleDB
Part 5 - Custom Module using Grafana
Intro
This is part 4 in a series starting here that runs through building an Azure IOT Edge solution. This post will run through setting up TimescaleDB to store data published from the dotnet, python and node temperature modules.
The code is located at: https://github.com/chrismckelt/edgy
TimescaleDB: An open-source database built for analysing
time-series data with the power and convenience of
SQL — on premise, at the edge or in the cloud.
Steps to add the database
1. add the custom module
2. add the section to the deployment file
Expose the internal port 5432 that TimescaleDB uses to 8081 for external container use
{ | |
"TimescaleDB": { | |
"version": "1.0", | |
"type": "docker", | |
"status": "running", | |
"restartPolicy": "always", | |
"settings": { | |
"image": "${MODULES.TimescaleDb.debug}", | |
"createOptions": { | |
"HostConfig": { | |
"ExposedPorts": "5432/tcp", | |
"PortBindings": { | |
"5432/tcp": [{ | |
"HostPort": "8081" | |
}] | |
} | |
} | |
} | |
} | |
} | |
} |
3. create the docker file
FROM timescale/timescaledb:latest-pg11 | |
ENV POSTGRES_USER postgres | |
ENV POSTGRES_DB postgres | |
ENV POSTGRES_PASSWORD m5asuFHqBE | |
COPY init.sql /docker-entrypoint-initdb.d/ |
4. create the database, login and schema
CREATE TABLE IF NOT EXISTS table_001 | |
( | |
"Timestamp" timestamp with time zone not null, | |
IsAirConditionerOn smallint not null, | |
Temperature decimal not null, | |
tagkey varchar not null | |
); | |
ALTER TABLE table_001 OWNER TO postgres; | |
SELECT create_hypertable ('table_001', 'Timestamp'); | |
-- Grafana access | |
CREATE USER grafana WITH PASSWORD 'YOUR_PASSWORD' CREATEDB; | |
CREATE DATABASE grafana OWNER grafana; |
5. run the container and insert data from another module
var connString = "Server=timescaledb;Port=5432;Database=postgres;User Id=postgres;Password=PASSWORD;"; | |
using (var conn = new NpgsqlConnection(connString)) | |
{ | |
string sql = $"insert into Table_001 VALUES ('{p.TimeStamp}',{p.IsAirConditionerOn},{p.Temperature},'{p.TagKey}')"; | |
await conn.OpenAsync(); | |
using (var cmd = new NpgsqlCommand(sql, conn)) | |
{ | |
await cmd.ExecuteNonQueryAsync(); | |
} | |
} |
select * from “table_001” where Isairconditioneron = 0 ORDER BY “Timestamp” DESC LIMIT 100;
Outro
Now we have data being saved into the database we can move onto displaying it visually via Grafana in the next post.
Top comments (0)