Skip to content
Use cases

IoT Database

CrateDB powers IoT solutions by managing vast volumes of real-time data from millions of connected devices. With its scalable ingestion engine and automatic indexing, CrateDB enables sub-second analytics, allowing businesses to monitor, analyze, and act on IoT data instantly. Unlock deeper insights and drive smarter decisions with CrateDB’s IoT-ready platform.

Sensor data queries with SQL

Hyper-fast. Results in milliseconds.

 

        

/* Based on IoT devices reports, this query returns the voltage variation over time
for a given meter_id */ WITH avg_voltage_all AS ( SELECT meter_id, avg("Voltage") AS avg_voltage, date_bin('1 hour'::INTERVAL, ts, 0) AS time FROM iot.power_consumption WHERE meter_id = '840072572S' GROUP BY 1, 3 ORDER BY 3 ) SELECT time, (avg_voltage - lag(avg_voltage) over (PARTITION BY meter_id ORDER BY time)) AS var_voltage FROM avg_voltage_all LIMIT 10;
        

+---------------+-----------------------+
|          time |           var_voltage |
+---------------+-----------------------+
| 1166338800000 | NULL                  |
| 1166479200000 |   -2.30999755859375   |
| 1166529600000 |    4.17999267578125   |
| 1166576400000 |   -0.3699951171875    |
| 1166734800000 |   -3.7100067138671875 |
| 1166785200000 |   -1.5399932861328125 |
| 1166893200000 |   -3.839996337890625  |
| 1166997600000 |    9.25               |
| 1167044400000 |    0.4499969482421875 |
| 1167174000000 |    3.220001220703125  |
+---------------+-----------------------+
        

/* Based on IoT devices reports, this query returns the voltage corresponding to
the maximum global active power for each meter_id */ SELECT meter_id, max_by("Voltage", "Global_active_power") AS voltage_max_global_power FROM iot.power_consumption GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
        

+------------+--------------------------+
| meter_id   | voltage_max_global_power |
+------------+--------------------------+
| 840070437W |                   246.77 |
| 840073628P |                   246.69 |
| 840074265G |                   246.54 |
| 840070238E |                   246.35 |
| 840070335K |                   246.34 |
| 840075190M |                   245.15 |
| 840072876X |                   244.81 |
| 840070636M |                   242.98 |
| 84007B113A |                   242.93 |
| 840073250D |                   242.28 |
+------------+--------------------------+

Data interoperability

CrateDB offers data collection and storage for any type of data source: sensor data, historical data, geospatial data, operational parameters, environmental conditions ...

You can store complex objects in the database, before even knowing how you want to model your data. You can also add new data types and formats on the fly, thanks to dynamic schema capabilities. There is no need for human intervention and no need to synchronize multiple databases.

Scalability and performance

With CrateDB, data is available for query instantly after data ingestion. You get response-time in milliseconds - even for complex ad-hoc queries across billions of records - thanks to a fully distributed query engine. This gives you real-time insights and responsiveness.

CrateDB computes data aggregations on the fly thanks to columnar storage. There is no need to downsample or pre-aggregate the data.

To meet the stringent requirements of industrial environments, CrateDB can scale easily. This way, you can cope with the huge volumes of IoT data continuously flowing from different sources.

Time-series

CrateDB has all the features of an advanced time-series database. This includes instant access to years of data, thanks to a distributed architecture with efficient sharding and partitioning mechanisms.

CrateDB also provides efficient storage, and instant querying of time stamped data to enable trend analysis, forecasting, and historical comparisons.

Fault tolerance built-in

CrateDB has built-in data replication and cluster rebalancing mechanisms. This provides fault tolerance and high availability for 24/7 operations.
cr-quote-image

Easy and seamless integrations

CrateDB offers a seamless integration with popular IoT stack software such as Kafka, Grafana, Node-RED, and more. It uses for that native SQL and the PostgreSQL Wire Protocol.

Drivers and libraries are available for many programming languages, as well as a REST API.

cr-quote-image

Edge computing

IoT projects often require a deployment at the Edge and in the cloud to enable real-time decision-making and analytics in various connectivity scenarios.

CrateDB effectively addresses this need by offering an IoT database with multiple deployment options. It supports both on-premises, Edge, and cloud environments.

A Docker container can be deployed on Edge devices and data can be synchronized with other clusters thanks to logical replication.

cr-quote-image

Digital twins

CrateDB offers a unique repository to store and retrieve metadata associated with digital twins. This includes information about the physical entity, data sources, data quality and modeling assumptions.

Time-series data can be contextualized with this information in real-time. An easy switch can be made from a technical view to a business view.

Integration with data analytics and AI technologies make it fluid to run complex algorithms, machine learning models, and statistical analysis directly on the stored data. 

Open source

CrateDB open source licensing model reduces your IT costs and provides the support of an active community.

Whether you want to get everything managed for you with the SaaS model or prefer deploy the product yourself, we have the right option for you if you decide to go for a fast and scalable IoT database. 

Talk - Not All Time-Series are Equal

This talk at the IoT Tech Expo 2023 explores the complexities of industrial big data, characterized by its high variety, unstructured features, and different data frequencies. It also analyzes how these attributes influence data storage, retention, and integration when dealing with an IoT database.

Talk - ABB: AI and Analytics applied to Industrial Data

In this talk, Marko Sommarberg, Lead Digital Strategy and Business Development at ABB, explaine how ABB Ability™ Genix applies AI and analytics to unlock the value of industrial data using CrateDB.

IoT Device Integration with Python

        

# Send IoT data to CrateDB with a simple HTTP request

import requests
import json

from datetime import datetime

def send_iot_data(device_id, temperature, humidity):

    url = "http://localhost:4200/_sql"
    headers = {'Content-Type': 'application/json'}
    payload = {

        "stmt": "INSERT INTO iot_data (device_id, temperature, humidity)
        VALUES ('{device_id}', {temperature}, {humidity})"
    }

    response = requests.post(url, headers=headers, data=json.dumps(payload))

    if response.status_code == 200:
        print("Data sent successfully!")
    else:
        print(f"Error sending data. Status code: {response.status_code}")

# Example Usage

send_iot_data('device-3', 22.5, 58.3)
        
        
        

Want to know more?

User stories

ABB Ability™ Genix optimizes operations and increases asset availability in industrial use cases by analyzing vast amounts of data in real-time. They use CrateDB to unlock the value of industrial data, working with advanced data analysis and data management capabilities. With a data ingestion rate of an 1 million values per second and event retrieval ranging from 30,000 to 120,000 events per second, ABB optimizes industrial efficiency and productivity.

"Working with CrateDB brings positive outcomes. The ingestion and throughput have very good performance, with 1 million values/sec, the horizontal scalability where we can add as many nodes as we need and the automatic query distribution across the whole cluster"

Marko Sommarberg
Lead, Digital Strategy and Business Development at ABB

cr-customers-abb
SPGo! is part of PETROMIN, which has more than 23 years of experience in the mining and oil industries. They build applications for monitoring all material conveyor belt idlers every minute 24 hours a day through online sensors. They use CrateDB as a central database to capture and query data from 30,000 sensors per mine, representing 760 million records a day.

"With CrateDB, we can continue designing products that add value to our customers. We will continue to rely on CrateDB when we need a database that offers great scalability, reliability and speed."

 

Nixon Monge Calle
Head of IT Development and Projects
SPGo! Business Intelligence

SPGo
Gantner Instruments collaborates with the University of Cyprus to operate a state-of-the-art Smart Micro Grid, dedicated to investigating the control capabilities of renewable energy sources in the power grid and propelling the energy transition forward. They leverage CrateDB to analyze the vast amount of data generated in real time, enhancing their processes through machine learning (ML). With CrateDB, they gain access to their extensive data within microseconds at the frontend, ensuring optimal performance.

"CrateDB is the only database that gives us the speed, scalability and ease of use to collect and aggregate measurements from hundreds of thousands of industrial sensors for real-time visibility into power, temperature, pressure, speed and torque."

Jürgen Sutterlüti
Vice President, Energy Segment and Marketing at Gantner Instruments.

cr-customers-gantner

Additional resources

In the world of Internet of Things, managing the volume, variety, and speed of data collected is a constant challenge. With its fast data ingestion, versatile data modeling, and real-time querying capabilities, CrateDB helps businesses harness the full value of high-volume data from thousands of IoT sensors.

Traditional IoT database systems frequently struggle to keep up with the massive volumes of data generated by thousands of sensors. CrateDB overcomes this challenge by providing fast, scalable data ingestion, processing millions of IoT data points per second. It indexes and makes data available for query in real-time. This enables businesses to achieve real-time monitoring, improved operational efficiency, and faster decision-making.

Another challenge is to connect data with digital twins, which offer a way to bridge the gap between the physical and digital worlds. Whether it’s for predictive maintenance, smart building solutions, data driven energy transition or raw material waste reduction, digital twins offer huge potential to improve operational efficiency and position enterprises for future growth. CrateDB significantly enhances the effectiveness and capabilities of digital twin implementations. For that, it executes complex ad-hoc queries very fast, reduces development efforts and optimizes total cost of ownership.

FAQ

An IoT database is a specialized storage system designed to manage and process the vast amounts of data generated by Internet of Things (IoT) devices. These databases are optimized to handle high-speed data ingestion, storage, and retrieval from numerous devices in real-time. They support diverse data types and provide advanced analytics capabilities to derive actionable insights from IoT data streams.

Examples of such IoT databases include CrateDB. InfluxDB, MongoDB, and Cassandra. CrateDB's helps businesses harness the full value of high-volume data from thousands of IoT sensors with its fast data ingestion, versatile data modeling, and real-time querying capabilities. Its uniqueness resides in its performance, multi-model support and Edge coverage.

IoT data types primarily include structured data (e.g. sensor readings and timestamps), semi-structured data (e.g. JSON payloads), and unstructured data (e.g. images, video files, and audio recordings). The diversity of these data types requires a flexible database capable of efficiently managing and processing various formats to support different IoT applications. In CrateDB, you can store any type of data—structured, semi-structured, and unstructured—and leverage native SQL to query the data, as well as advanced time-series and search functionalities. CrateDB offers data collection and storage for any type of IoT data source: sensor data, historical data, geospatial data, operational parameters, and environmental conditions.

IoT data is stored in databases designed to handle large-scale, real-time data. Smart partitioning strategies are key to manage multiple periods of time efficiently. Depending on the specific application and requirements, these databases can be on-premises, cloud-based, or edge-based. CrateDB is highly flexible and can be deployed on private or public cloud, on-premises, or edge to meet your organization's unique needs. It also supports hybrid scenarios out of the box.

An IoT database architecture typically includes several key components: IoT devices that generate data, a network for transmitting this data, a storage system for data accumulation, and an analytics component for data processing. The architecture is designed to manage data flow from devices to storage and ultimately to analysis platforms, ensuring timely and accurate insights. CrateDB addresses these needs by providing easy and seamless integration with popular IoT stack software such as Kafka, Grafana, and Node-RED, leveraging native SQL and the PostgreSQL Wire Protocol.

An IoT database needs to manage large volumes of data, support a variety of data types, and offer real-time data processing capabilities. It's crucial for it to be highly scalable in order to handle the increasing number of devices and data streams. CrateDB meets these requirements by providing instant query availability after data ingestion, offering millisecond response times even for complex queries across billions of records. This ensures real-time insights and responsiveness, making CrateDB the right choice for IoT applications.