DEV Community

Chris McKelt
Chris McKelt

Posted on • Originally published at blog.mckelt.com on

5 3

Azure IoT Edge – using TimescaleDB on the Edge

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;
view raw TimescaleDB.sql hosted with ❤ by GitHub

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();
}
}
view raw TimescaleDB.cs hosted with ❤ by GitHub
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.

Image of AssemblyAI tool

Transforming Interviews into Publishable Stories with AssemblyAI

Insightview is a modern web application that streamlines the interview workflow for journalists. By leveraging AssemblyAI's LeMUR and Universal-2 technology, it transforms raw interview recordings into structured, actionable content, dramatically reducing the time from recording to publication.

Key Features:
🎥 Audio/video file upload with real-time preview
🗣️ Advanced transcription with speaker identification
⭐ Automatic highlight extraction of key moments
✍️ AI-powered article draft generation
📤 Export interview's subtitles in VTT format

Read full post

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay