How to collect and query IoT data With MQTT, JSON & SQL

Shane Johnson
Thank you for joining today’s webinar, “How to collect and query IoT data with MQTT and SQL.” . . . My name is Shane Johnson. I’m running marketing here at FairCom. Prior to this, I ran product marketing at Couchbase, and way, way back, I was a Java developer and architect at Red Hat and a few other places. Suffice to say, I’ve been around databases for quite a while.

I’m going to cover a majority of the material here today, but we’re also lucky to have Mike Bowers here with us.

Mike Bowers
Hello, I’m Mike Bowers, I’m the Chief Architect at FairCom. And I’m here to support and help Shane answer any questions. I designed the product and I’m helping a team that’s QA-ing it. I’m very interested in all your feedback, so we can improve the product and make it better for you. So with that, I’ll turn it back over to Shane.

Shane Johnson
Thanks, Mike. And you guys can consider that the gauntlet being thrown. Feel free to ask the hardest, toughest questions. That’s why we have Mike here.

Alright, a little bit about the agenda.

We’ll talk about MQTT for a few minutes. And we’ll certainly get into some concepts a little bit around the features. While this isn’t necessarily a deep dive into MQTT, iif you want more of that in the future, let us know.

Then we’ll spend a little bit more time talking about FairCom Edge and give a product overview. We’ll go through some solutions, ways that it can be used and deployed, and then we’ll spend a little while on persistent topics. This is really where the magic is—you’ll see this when we start to talk about the product overview and the architecture. And we’ll conclude with a live demo.

How the Internet of Things Connects Everything Around Us

Shane Johnson
I thought I’d start with a little bit of a personal story here to illustrate how everything is connected. We’ll cover the technology used in the Johnson family home over the past decadel. Early on, it was the usual—laptops, computers, printers, phones.

Then, all of a sudden, it’s my home theater coming to life. The Xbox One is connected, even the receivers are connected. And now all of our TVs are connected as well. My wife wants an Apple Watch? It’s connected.

Then I had an issue. I’m in California, and it’s really hot here in the summer. I’d been given some advice from a colleague; he had this connected irrigation system so he could understand what the temperature is and when it was expected to rain. So, of course, I went and got one of those guys and put it in. Then, I had an obsession, about two years ago, around lighting. I started with these whiz lights and WiFi lights that I could control with my phone. And then I added TV lighting with these really cool strips that go behind the TV and corner the walls. Then I got on the whole GoogleNest setup—the locks, the video camera doorbell, thermostats. And then these hubs, one in the kitchen, one of my office.

My wife wanted a Peloton bike, and I followed suit with a treadmill. Both of these are connected to the internet as well. But it doesn’t stop there. I’ve been looking at a Tempo, which is a kind of smart home gym, and I’ve realized that our day-to-day appliances are all connected now. You can see what’s inside your fridge from your phone when you’re traveling. You can preheat your oven while you’re on your way home from work. I even bought a toilet that was connected to the internet. It had lighting and various other capabilities associated with it.

Then there’s the potential for electric cars. I started looking at grills. Nowadays, these grills are connected too. You can take out your phone and set the temperature and monitor all these other aspects of it. Even garage door openers are connected. I had some issues with my garage the other day, and I thought I might have to get a new one. Turns out they’re connected too, and they come with cameras and sensors.

To put it shortly, I have a mesh network at home. When I opened up the app for it, I realized there were upwards of 50 or 60 connected devices. My mind was blown because I just hadn’t realized this was happening. I think I had just assumed that it was only my laptop and phone, etc. But then it hit me that there are literally dozens of devices in my home connected to the internet. It’s hard to imagine things that won’t be connected. I’ve started thinking about this even when I’m out and about. Way back, I actually remember counting shopping carts at a store that had RFID tags and sensors on them. And if you took them too far away from the store, the wheels lock.

When I moved to San Jose, you know, the first time we went to a parking garage, I was really fascinated. There’s this kind of dashboard at the entrance that tells you how many spots are open on each floor. And sure enough, as I made my way through the garage, driving around, I noticed above each spot, there was a little light that was either green or red—green if the spot is open, red if it was occupied. I thought that was really interesting. Then, as we did more traveling, we came across parking meters where you just have to install an app on your phone. You tell the app what spot you’re in and make your payment through a credit card.

Even more recently, I was chatting about microphones when I got back from vacation. We went to this place called Great Wolf Lodge, a resort with an indoor waterpark, restaurants, and various activities. When we checked in, we were all given these wristbands. We used these wristbands to pay for activities and meals, which was super convenient. I didn’t have to carry my credit card and driver’s license everywhere I went. Then, in the case of my trailer, we went to Chuck E. Cheese. When I was a kid, that meant putting cash in a machine to get tokens for playing games. But now, they have RFID wristbands. Kids just wave their wristbands in front of the games to play, and all the tickets they earn are associated with their online account. They can claim their prizes when they’re done. So, when I took a moment to think about it, everything’s connected; all kinds of devices.

And then, of course, there’s the concept of the smart trend. I only mentioned Smart City, Smart Power, and Smart Factory, but you could probably attach any noun after that term to describe how things are changing. It’s happening in every industry. MTD itself can trace its roots back to oil and gas, in particular, but certainly across various sectors like transportation, shipping, logistics, and telecommunications. I tend to get really fascinated by all things retail. Even in retail, they’re putting sensors in stores to understand the flow of traffic, where people are going, where they’re pausing, and where they’re stopping. So everything is changing. Everything we interact with is becoming connected to the internet.

And when I reflect on that a bit more, in my view, technology revolves around data, especially in this specific context. What we really need to unlock is the capability for all these diverse devices to both send and receive data.

How MQTT Works With IoT Data

Shane Johnson
If we frame this within the context of MQTT, it essentially means having the ability to publish data. Devices generate data and then send it to a certain location, or you have other devices that take action once they receive data. They subscribe to data in order to obtain it.

Considering the “whys” behind this, from a consumer perspective, I believe it’s closely tied to insight and automation. For instance, take the Google Nest thermostat. It’s valuable to be able to revisit a dashboard displaying my home’s temperature and compare it with my electricity bill – whether we’re present at home or not. Then there’s the aspect of automation. One reason I invested in the Google Nest Hub is the convenience of being able to walk around my home and say, “Hey, Google, lock the doors.” And it actually does it. It’s truly remarkable. Or I can say, “Hey, Google, it’s TV time,” and it will turn on the TV, the TV lights, the receiver, or the Xbox—all of it in one go.

From a business standpoint, there are likely numerous factors at play. However, I firmly believe that Artificial Intelligence (AI) and Machine Learning (ML) will play a central role in many of these scenarios. With access to all this data, businesses can enhance operational efficiency and productivity. This holds particularly true when we consider the context of the Industrial Internet or Industrial IoT. Additionally, data can aid in improving the customer experience. Let’s take the Google Nest thermostat as an example again. By analyzing the data, Google can adapt and optimize temperature settings for me. I won’t need to manually adjust the temperature at night, during my absence, or when it gets unusually hot outside. Google takes all these inputs and acts on my behalf.

Of course, all these instances ultimately translate into delivering greater value. This is perhaps most evident in the B2C (business-to-consumer) sphere. As Google becomes smarter in managing my Nest thermostat, I save money, enjoy comfort, and experience convenience without the hassle. Google provides more value to me. And this transformative process is all made possible by data accessibility—something that, if we cast our minds back 10, 15, or 20 years ago, would have seemed trapped within a black box.

As we delve into the topic of IoT, certain limitations emerge. Primarily, there are constraints concerning devices, especially when it comes to hardware. While Intel might possess 40 to 50 or even 60 core processors, it’s highly unlikely that a 60-core processor resides within devices like my Google Nest lock. Additionally, unreliable connections are a concern, particularly in industries like oil and gas or remote areas. The presence of low bandwidth and high latency raises questions about the impact. Simultaneously, the need for bidirectional communication arises.

Take, for instance, the lights in my house. Philips would find it beneficial to know their status, whether they’re on, off, or how much energy they consume. Yet, it’s also essential that the communication goes both ways, as I can instruct the lights to adjust. Ensuring guaranteed delivery is another challenge. In specific environments, data loss is unacceptable, especially for critical sensor readings, alerts, notifications, and alarms. If a message is dispatched, it must be assuredly received. Moreover, security must remain paramount. With numerous devices in my home, containing personal and sensitive data, I prefer not to share such information with the outside world when I’m away or if my doors are left unlocked. Security measures are expected in handling this data.

This is where MQTT comes into play.

What is MQTT, and What is it Used For?

Shane Johnson
MQTT is a remarkably lightweight protocol. While numerous messaging protocols exist, the context of constrained devices necessitates a lightweight choice—one with a small footprint and low resource consumption. The simplicity is essential since these devices are focused on data transmission and reception. Complexity is unwelcome in this context. MQTT’s standardized nature is the reason it’s becoming ubiquitous.

It adheres to a publish-subscribe approach. Essentially, data is published somewhere, and this occurs through a broker. Clients, whether the same ones or others, can then subscribe to this data via the broker. In essence, clients send and receive data while the broker hosts it. While there are additional capabilities and features, you can think of the broker as an intermediary in the diagram below. A sensor publishes, say, temperature data, clients like phones or climate control systems receive it, and the MQTT broker functions as the intermediary managing these messages. High-level features of the broker include message filtering, which is vital considering numerous clients publishing and subscribing to data. Not all clients are subscribing to the same data, and the broker must manage this effectively. The concept of quality of service is intertwined, touching on the notion of nearly guaranteed delivery mentioned earlier. Different levels exist—from non-guaranteed to exactly once and persistent sessions. Understanding that clients might disconnect abruptly due to factors like power loss or network issues, the broker needs to handle these scenarios. Upon reconnection, clients should ideally resume without missing any messages that were sent during their offline period.

All these features are present in MQTT 3.1. There are a few more additions. And more recently, MQTT 5 introduced several additional features. Some of these noteworthy ones include user attributes, enabling the insertion of name-value pairs within messages to provide enhanced metadata, as well as shared subscriptions. In shared subscriptions, a difference emerges compared to the typical MQTT behavior, where every client subscribing to a topic receives the same messages. In a shared subscription environment, multiple clients subscribing to a topic share the messages, somewhat akin to a round-robin distribution, where a message is received by only one of the subscribing clients. This approach spreads out the message distribution. Various other beneficial features also exist within MQTT 5.

Now, regarding tools, the MQTT ecosystem is expanding rapidly. I’ve provided a link for further exploration. Here are a few standout tools that I’ve identified. In terms of clients, almost every programming language has an available MQTT client. Numerous libraries are available as well. We’ll delve into one of our tools, MQTT Explorer, towards the end of the demo. There are MQTT clients designed for Android phones, plugins tailored for the Chrome browser, and even Command Line Interfaces (CLIs). These represent some of the actively maintained tools. However, as mentioned earlier, you can refer to the provided link to discover dozens more tools across various categories. MQTT makes it quite simple to get started.

This brings us to the product overview. I’d like to discuss FairCom Edge a bit, and the initial few slides will outline the architecture and how we assemble this solution.

FairCom Edge: The IoT Platform for Edge Computing

Shane Johnson
It all begins with FairCom DB, which serves as our database. This database is lightweight, high-performing, extremely optimized, and portable—capable of running anywhere. As expected, it supports SQL queries and additionally boasts a REST API for HTTP access.

Building upon this foundation, we have a collection of what we term “edge plugins.” These four, in particular, can be thought of as protocols. They enable FairCom DB to collect data through OPC UA or Siemens S7. These protocols are popular in the industrial sector, typically used by machinery and equipment. These devices commonly communicate using these protocols, and our edge plugins enable us to capture this data within FairCom DB. MQTT, which you’ve been introduced to, is also one of these protocols. Furthermore, our solution permits the collection of this data and its subsequent transmission to ThingWorx in the cloud. Irrespective of whether the data arrives via OPC UA or MQTT, we have the capability to extract these messages and direct them into ThingWorx.

Furthermore, there are a few supplementary plugins that won’t be covered in today’s session. We’re planning to delve into them during an upcoming webinar. These plugins revolve around automating message timestamping, performing continuous data aggregation, and even data purging—a process essential when disk space becomes limited.

This translates to the ability to capture and collect data from an array of machines, sensors, and connected devices. This could encompass anything from a wheel fabricator to temperature sensors, or as mentioned earlier, my thermostat or doorbell. We can effortlessly gather all this data.

Given our foundation built upon FairCom DB, we’re equipped to create custom dashboards using the REST protocol. We can even access all states to craft separate applications. Suppose an entirely distinct web application is under development. Moreover, through SQL JDBC and ODBC, traditional Business Intelligence (BI) and reporting tools can tap into the data. Essentially, we offer various avenues to ingest machine data and provide both new and existing ways to access it, extending beyond the realm of IoT.

Finally, as part of FairCom Edge, we provide web applications, both of which will be demonstrated in MQTT Explorer and SQL Explorer during the demo. This constitutes a comprehensive platform. While we’ll focus heavily on MQTT capabilities, the real magic arises from the synergy of all these elements. Yes, we boast an MQTT broker, enabling data publication and subscription. However, we also persist the data, map it to tables, offer it through SQL and REST, and furnish web interfaces via MQTT Explorer and SQL Explorer. This extends far beyond the capabilities of a conventional MQTT broker.

Multiple deployment options are available, partially attributable to FairCom DB’s lightweight nature. It can be pruned down to as little as 20 to 30 megabytes, requiring minimal memory and processing power for deployment. On the far left, you can even deploy it directly on devices, and there are advantages to this approach. Network connectivity dependency diminishes as you’re continually publishing data to your own system. Moreover, concerns about latency become less relevant in this context.

Alternatively, you have the option to deploy it on gateways. These gateways are typically specialized devices placed in retail stores, on factory floors, in gyms, or any other relevant location. Positioned at the edge but not on the devices themselves, these gateways serve as intermediaries. Conversely, you could deploy it in the cloud, where various instances are available, including substantial ones. This becomes an effective method for data aggregation across multiple locations. Picture a scenario where you have numerous devices in a single location – the data from these devices is aggregated at a gateway. These gateways might be spread across different locations within a country or even worldwide. Subsequently, these aggregated gateways can be further consolidated in the cloud. The decision on where to deploy FairCom Edge and the MQTT broker rests with you.

In my opinion, the sweet spot lies with the gateway approach. As I previously mentioned, you can place it directly on the device. Essentially, the device publishes messages to itself. Additionally, you can deploy FairCom Edge as a gateway, capable of subscribing to messages from devices. This approach works exceptionally well with gateways. It facilitates on-premises aggregation, lightweight analytics, and efficient messaging handling. Moreover, it can seamlessly transmit data to the cloud, whether it’s FairCom Edge in the cloud or your chosen MQTT broker like Azure IoT Hub.

Applications and Use Cases of IoT Data Management Platforms

Shane Johnson
Now, let’s explore potential solutions, which I find quite intriguing as they stimulate imaginative thinking. One aspect involves protocol conversion. Consider capturing data through OPC UA from multiple factory machines. Then, publish this data to Azure IoT Hub for cloud-based actions. Alternatively, you might gather MQTT data from diverse devices—whether it’s home security systems, alarms, or exercise equipment—and publish it to ThingWorx, if desired. In essence, we can acquire data in various protocols and then transport that data elsewhere in a different protocol.

Here are a few examples. This example aligns with a focus on industrial IoT. Imagine a factory with an array of machines and sensors. You acquire data from these through industry-standard protocols. Subsequently, this data is pushed to Azure IoT Hub using MQTT. Moving on to the concept of multiple locations—if you have numerous factories, each can deploy FairCom Edge. Some factories might predominantly utilize OPC UA, while others rely on Siemens S7, or perhaps a mixture of both. Notably, FairCom Edge doesn’t discriminate based on protocols. It can aggregate data from each factory, followed by pulling this aggregated data into Azure IoT Hub. This kind of global data aggregation occurs in the cloud.

Here’s another familiar example: MQTT to ThingWorx integration. This setup provides a distinct, straightforward pathway to channel data into ThingWorx. Thanks to FairCom’s solution, the process is incredibly streamlined through FairCom Edge. Whether it’s a factory floor, a retail store, a gym, or even an amusement park, MQTT data is seamlessly collected at the edge and then transmitted to ThingWorx for utilization.

A parallel scenario arises when considering multiple locations—envision numerous amusement park sites or even hotels. Each hotel could deploy FairCom Edge, gathering MQTT data such as room temperatures and door statuses. This data is then transmitted into ThingWorx across the network.

Of course, FairCom Edge can also function as a straightforward MQTT broker. If your focus revolves solely around MQTT and excludes other protocols, this approach serves perfectly. Within this landscape, some devices publish data, some subscribe to it, and others do both. Additionally, it’s worth mentioning that FairCom Edge can subscribe to data from other MQTT brokers. This allows it to act as a client and subscribe to data from an existing MQTT broker. Conversely, other brokers could subscribe to FairCom Edge, further distributing its data.

Understanding Persistent Topics and MQTT

Shane Johnson
Now, transitioning to the topic of persistent topics—this is where things take an intriguing turn, as I mentioned while discussing the architecture. I consider this the most fascinating aspect. Traditional MQTT brokers tend to store messages in memory, often with a fixed memory limit. When this limit is reached, message purging becomes necessary. Furthermore, if the broker is restarted, any messages stored in memory are lost. Some brokers offer additional persistence options, which might vary based on the specific broker in use. However, FairCom Edge takes a unique approach with its concept of persistent topics. These topics are stored in tables on disk, offering exceptional flexibility.

During the demo, I’ll illustrate this concept in more detail. You can store these persistent topics in user-defined tables. You specify the table name and column names to organize the data. Additionally, you can map these topics to existing tables if that suits your requirements.

We often work with JSON payloads. Assuming the payload is a JSON document, we can take it a step further. We parse the documents, extract properties, and then place them in various columns within our table. However, before delving deeper into this, let’s switch topics.

Let’s look at persistent topics and how we manage them in FairCom Edge, after it’s installed. It actually has the c-tree administration topic. You can publish messages to that topic to create persistent topics, or pause them, or delete them.

And what you see here is just a very simple example. We’re using the operational property, create persistent topic. Or if we wanted to pause it, it would be pause, persistent topic.

Then there’s the persistent topic property—that’s your topic name. Think about a bar and bottle shop. Being a bottle shop means they have lots of lots of coolers, cans, and bottles of beer. So, how IoT would apply to the shop? They would want to monitor the temperature of the cooler. So I have a topic—a district, which could have multiple stores—and then a store could have multiple coolers, and then the coolers all have a status. This is where my messages are going to be published. And I have two simple properties, the cooler ID and the temperature.

In the JSON document, it’s called cooler ID. But in my table, I just want it to be called “ID.” One is called “Temperature,” but in the JSON document, I want it to be “Temp.” So we’re going to parse these JSON documents. Even if they have arrays and deeply nested objects, we can parse all that. We can map JSON types into standard database types. This is defined when we create our topic either through the JSON document or visually through the MQTT Explorer. There’s also a lot of optional properties.

With FairCom Edge, you can store binary data in JSON documents, even though it’s not an official data type. JSON documents are strings, but they have a certain format. So this external broker storing in JSON documents is pretty interesting. FairCom Edge could be deployed as an MQTT broker.

But if you have other sources available, we can seamlessly subscribe to them and pull that valuable data into our system. This process mirrors our approach for managing our own topics. Now, let’s circle back to the concept of field mapping. You might have noticed a key property towards the end, and here’s where I’ll pivot the discussion slightly and approach it from the opposite direction. Let’s begin by dissecting the messages generated by our devices.

Consider a scenario involving a specific shop equipped with one or two coolers. These coolers are at the heart of message generation, relaying essential details such as the current temperature and the status of the cooler doors—whether they’re open or closed. For instance, one message could indicate a cooler temperature of 150 degrees with a closed door, while another message might describe a different cooler with a temperature of 45 degrees and an open door. These messages carry straightforward sensor readings.

Now, let’s explore how we translate this message structure into a compatible database schema. Enter the concept of field mapping. I’ll start by revisiting the “create topic” message we recently discussed. However, the real gem here lies in the fourth property—mapping properties to fields. To execute this, we specify a property path, such as “cooler ID,” aligning it with its corresponding field in the message. Simultaneously, we determine the desired field name within the database. For instance, we might opt to rename “cooler ID” as simply “ID,” and “Temperature” as “Temp.” Similarly, “door open” could be transformed into “open” within the database columns. This meticulous mapping ensures seamless integration. Armed with the knowledge of how these messages are structured, we can confidently create our topic, knowing that messages will fit snugly within this configuration. As a result, the stored messages will seamlessly align with this pattern within the database. In essence, the three messages we discussed—containing cooler information, door status, and temperature—are effortlessly stored as individual rows within a designated table, often referred to as the “cooler status table” or “TBL cooler status.”

Now, let’s broaden our perspective. Consider large coolers often featuring multiple doors. It’s conceivable that messages will be dispatched at varying intervals—perhaps every second or minute—conveying the temperature and the status of each individual door. This adds a layer of complexity to the equation.

Yet, this complexity also heralds exciting possibilities. Leveraging this data, we can dive into the realm of consumer purchases. By juxtaposing door status information with purchasing trends, we might uncover patterns among specific types or brands of beer. A captivating application emerges—cooling optimization. Rather than patrons opening all three doors and inadvertently impacting the temperature, a well-organized layout could encourage them to access their desired items behind a single door, thereby preserving a consistent temperature. So, here’s where the fun applications of this IoT data come into play.

Using Arrays with MQTT and FairCom Edge

Shane Johnson
But things get a bit trickier when we introduce arrays into the mix. Picture this: mapping an array structure is a bit like solving a puzzle. I’ve got an example on screen. I’ve condensed the details a bit to focus on the core concept. As we delve into the properties-to-fields mapping process, you’ll still recognize elements like the cooler ID and temperature. But the twist comes in when we encounter an array named “doors.” We tell our system that within this array, a child table called “TBL door status” exists. And then, we proceed to map the properties within this array. For instance, “door ID” is linked to the “ID” column, while “door open” corresponds to the “open” column. This array transformation might look a little something like this:

Imagine a message that arrives at 9 a.m., indicating a temperature of 50 degrees. Concurrently, another table displays the states of each of the three cooler doors—whether they’re open or closed. This complex arrangement is handled seamlessly by FairCom Edge. It automatically infuses the primary key and timestamp into the initial table and ensures a consistent structure by integrating foreign keys and position columns within the child table. In simpler terms, it’s like fitting puzzle pieces together, translating these intricate JSON documents into well-structured relational tables underneath the surface.

Using Store and Forward in an MQTT Broker

Shane Johnson
Now, let’s delve into a relatively new feature: store and forward. Think of it as the bridge connecting past and present. When our devices reconnect, they pick up right where they left off, ensuring no message is lost, even if a device temporarily goes offline. This has intriguing implications. Imagine a cooler controller—maybe it went offline due to a power issue. While disconnected, it continues generating messages that stack up like a queue. When it finally reconnects, FairCom Edge guides it through a seamless catch-up process, ensuring it retrieves every message missed during its offline stint.

Here’s where things get even more interesting. New clients can now opt to catch up from the very beginning, diving into the archive of messages stored before they even joined the party. It’s like jumping into a movie halfway through or starting from the very opening scene. This ability to access messages from the oldest stored ones—rather than just the next in line—opens up fresh avenues for exploration.

Now, let’s explore some of the knobs and switches that control this magic. I’m talking about properties like enabling store and forward, adjusting delivery rates, and deciding the maximum number of messages to store at any given time. These are the levers that allow us to tailor this data management process to our unique needs. It’s all about finding that sweet spot—ensuring we store enough but not too much, and delivering messages at a pace that suits our requirements.

And with that, let’s transition to the exciting part: the live demo. Now, I must admit, I’ve been both the eager presenter and the cautious developer, praying for a smooth demo. So, I’m going to share my screen and give you a sneak peek into the world of MQTT Explorer. Can you all see my screen okay? If not, please give me a shout. Assuming everything is in order, let’s dive right in.

Demo: FairCom Edge in Action

Shane Johnson
As we log in and navigate to the Topics tab, I’m about to create a new topic that takes us deeper into this journey. I’m thinking of a topic structure that involves districts, stores, pools, and statuses. We’ll tweak it slightly to align with our data, ending up with “TBL cooler status.” Within this topic, we’ll define properties like cooler ID, temperature, and door open. Each of these properties will play a crucial role in capturing our cooler data.

Now, I’m going to skip ahead a bit since the technical details are rather specific, and we’re looking for a more high-level understanding. Suffice it to say, the JSON preview shows that we’ve successfully created a new persistent topic—a significant step forward. With that in place, we’ll head to the subscription side. We’re instructing our application to subscribe to this topic, effectively setting up the stage to receive and interact with the data. And as we move to the publishing step, I’ve got some ready-made examples to simplify things.

So, let’s dive into a specific scenario using our imaginary cooler data. In one of the messages, picture cooler one—it’s a chilly five degrees Celsius, and its door is wide open. Now, let’s shift gears for a moment. In another message, the story changes—cooler one’s temperature has spiked to a balmy 60 degrees, and yet, its door remains open. Now, this isn’t just about data—it’s about weaving a narrative using these messages. But how do we see all of this in action? Well, let’s head over to the monitor tab right here. By leveraging MQTT, we’re giving this browser-based app its very own embedded MQTT client. Whenever I publish a message from this app, the monitor subscribes to it, revealing the messages that come to life. It’s like watching the script unfold in real time. And that’s not all—we can also explore the data tab. A quick refresh, and lo and behold, we encounter our “TBL cooler status” table. Look at that! It’s brimming with records that capture the essence of our cooler escapades.

But wait, let’s switch things up a bit. What if we decide to delete this topic? Brace yourself for a warning, because once this topic goes, so does the underlying table and all its precious data. But for the sake of this exercise, let’s go ahead and do it. The truth is, it’s all right—we’ve served our purpose. So, with a sense of finality, we delete the topic. But that’s not the end of the road. In my bag of tricks, I’ve got a more intricate example, one that involves NASA data. This example takes the concept a step further. You see, this persistence topic UI can be a powerful tool for creating topics—much like we did before. But this time, instead of our cooler topic, we’re switching to an administration topic. And in the payload, there’s JSON magic—a message that commands the creation of a new persistence topic. Notice the addition of a “doors” array, revealing a whole new layer of complexity. With a swift publish, the deed is done. A new topic emerges, each element meticulously defined.

In the midst of these maneuvers, our operations are crystal clear. We publish our instructions via the c-tree administration topic. Every message is a directive that shapes our ecosystem. Once more, we venture to the topics section, hit refresh, and there it is—an embodiment of our command. Our new topic materializes, ready to welcome messages of its own. The journey doesn’t stop here. As we navigate to the Publish tab, we encounter our transformed status topic. This time, it’s different—cooler ID, temperature, and three doors with distinct states. Publish, and the message travels like a ripple in a pond. It’s all part of this intricate dance we’ve been orchestrating.

Now, if we revisit the data tab and refresh once again, we’re greeted by a landscape that has evolved. Behold, two tables stand before us—cooler status and door status. The messages have materialized into these structured representations. The temperature is now 55 degrees, and the doors tell their own stories—two closed, one ajar. And with a few more keystrokes, we paint new pictures. The temperature climbs to 60 degrees, as all three doors embrace the open stance. The real-time interaction with data continues.

But what if we journey deeper into the realm of SQL? A refreshed connection, and there they are—our tables, standing proud. Cooler status and door status—they’re not just data, they’re windows into a world of possibilities. Our SQL queries breathe life into these tables, uncovering insights that shape our perspective. Imagine querying for the average temperature, grouped by cooler ID. The answers are right there, waiting to be unearthed. It’s a symphony of data, tools, and exploration.

And so, we reach the crescendo of our presentation. As I stop sharing my screen, I hope each of you has taken away a piece of this journey. If you’ve found value in our time together, please consider sharing your thoughts in the survey. Your feedback fuels our continuous improvement. And now, as we wrap up this session, the floor is open to any questions you might have about FairCom, FairCom Edge, MQTT, or any related topic. Your curiosity guides our dialogue, so feel free to ask away!

IoT Q&A With Chief Architect Mike Bowers

Mike Bowers:
So Shane, I see there are questions from Michael about purging data, is that similar to garbage collection? The simple answer would be no, as we are developed in C and C++, garbage collection issues don’t arise. Our performance is consistent and predictable across all our built-in components. We do offer a data purge function to address the data collected from various sources, such as MQTT, OPC UA, REST, or SQL. This function allows automatic purging of data at a configured rate. So if you want to keep your data disk usage in check by purging data that’s over a certain age, like a month, a year, or even a day old, we can handle that automatically. This feature prevents data from growing uncontrollably, ensuring that your storage remains manageable.

Another question revolves around the best IoT operating systems for FairCom DB at the edge. It’s an excellent query and depends on your use case. FairCom DB suits various hardware, from devices to gateways to servers, ranging from small to large. For instance, if you’re dealing with a gateway use case, Linux or Windows is quite common. On the other hand, if you’re setting up an MQTT broker in a factory, which involves not just brokering but also data transformation and aggregation from multiple sources like OPC UA or REST and TGT, Linux or Windows work well. Our support for plugins means you can integrate any protocol and deliver data via different protocols, like MQTT or OPC UA, making Linux a favorable choice for most gateways. When it comes to devices, real-time operating systems like KonTiki or FreeRTOS are ideal. Our commitment to C and C++ code ensures portability, and we’ve successfully ported FairCom to over 80 operating systems in our 40 years of business. So, whatever your needs, we can work with you to support any operating system your use case demands. Android also stands out, being a fantastic platform for edge use cases. It’s versatile and runs on a wide range of devices, including phones, tablets, and even augmented reality glasses with Android OS. We’re also exploring support for iOS devices in the future. So, the choice of operating system depends on your use case, whether it’s a small device OS, a gateway, or a mobile OS like Android.

Shane Johnson
Thank you for addressing those questions, Mike. We appreciate your insights and expertise. As we conclude, I want to thank everyone who attended today’s session and submitted questions. Special thanks to the demo gods for smiling upon us during the live demo. If you could take a moment to complete the survey, your feedback will help us improve and tailor future webinars to your interests. As we wrap up, remember that FairCom Edge is more than just a database—it’s a comprehensive toolset that empowers you with the ability to store, query, and remotely access your data with authorized security.

It’s about efficiency and simplicity—just unzip and unleash a world of capabilities: an MQTT broker, REST API, SQL database, and more, all integrated seamlessly. With browser-based interfaces like SQL Explorer and MQTT Explorer, you can dive into your data and uncover insights effortlessly. Thank you once again, and we look forward to seeing you in our next webinar. Have a wonderful day, everyone. Goodbye!