CrateDB & Crosser.io - How to connect

As industrial environments become more data-driven, organizations face the challenge of efficiently integrating, processing, and analyzing massive streams of real-time data. The combination of CrateDB and Crosser offers a solution to break down data silos, optimize industrial analytics, and enable seamless edge-to-cloud integration. 

CrateDB’s distributed SQL engine, designed for high-performance time-series analytics, complements Crosser’s low-code data processing platform, which simplifies real-time data ingestion and transformation across edge, on-premise, and cloud environments. Together, they deliver low-latency insights and scalable data pipelines, empowering enterprises to enhance operational efficiency while reducing complexity and costs.

In this article, we will explore the technical integration of CrateDB and Crosser, detailing how organizations can combine both to unlock real-time intelligence from industrial data.

This reference architecture illustrates how Crosser and CrateDB integrate to create a seamless, real-time industrial data pipeline. Crosser nodes ingest and process data from diverse sources, including industrial edge devices, enterprise systems, and cloud environments, before storing it in CrateDB’s distributed SQL database for high-performance analytics. The Crosser Control Center orchestrates configurations and workflows, ensuring efficient data flow across firewall boundaries. The data can be enriched on CrateDB with technical documentation and maintenance data for more complex scenarios. On the consumer side, applications leverage CrateDB’s native SQL to enable real-time analytics, AI/ML workflows, and predictive intelligence, together with GenAI use cases.

Use Case

To illustrate a simple data flow, we will read real-time OPC UA data and insert it into CrateDB. Once stored in CrateDB, we can do complex real-time analysis, observe trends in the data, or compare it to older data. OPC Unified Architecture (OPC UA) is a machine-to-machine protocol to exchange messages in industrial automation. We poll it once per second to retrieve values for four metrics in total – two temperature readings and two pressure readings. These readings are then persisted to CrateDB.

Prerequisites

Before implementing a Crosser workflow, we need to prepare both Crosser and CrateDB.

CrateDB

We will use a CrateDB Cloud cluster to store data. If you don’t have CrateDB running yet, you can register for CrateDB Cloud and start a free cluster on our infrastructure. Note down connection details shared with you during creation of the cluster. See our Quick Start guide for more details.

Crosser Cloud

You can start for free with Crosser Cloud as well. Once registered, we need to store credentials for connecting to our CrateDB Cloud cluster. After navigating to “Manage”, “Credentials”, and clicking “Add Credential”, we can provide those details. Choose “Connection String” as the type and provide it in the following format: Server=<your CrateDB cloud hostname>;Database=doc;User ID=admin;Password=<your password>;

Implementation

As a first step, we create an OPC UA module in Crosser and analyze how our data is structured. Besides the OPC UA server URL, we configure a few additional tags to monitor, and the publish interval of one second.

OPA UA Subscriber configuration

We can run the workflow in debug mode to understand how retrieved data is structured and will see an output such as below.

Example of an OPC UA message

This gives us a good indication of how to model the target table in CrateDB. We choose a key/value type of table layout where the different metrics are stored as individual rows. For handling large amounts of data over time we choose to partition the data by day, see Sharding and Partitioning for more details.

CREATE TABLE crosser.metrics (
  timestamp TIMESTAMP,
  timestamp_day TIMESTAMP GENERATED ALWAYS AS DATE_TRUNC('day', timestamp),
  metric_name TEXT,
  status_code TEXT,
  node_id TEXT,
  value DOUBLE,
  PRIMARY KEY (metric_name, node_id, timestamp, timestamp_day)
)
PARTITIONED BY (timestamp_day);

The complete Crosser workflow consists only of three modules:

The complete workflow

After data is retrieved from the OPC UA Subscriber, we pass messages to a property mapper module. The primary objective of that module is to select the particular properties we are interested in for saving in CrateDB, as well as to align property names with our target table column naming:

Configuration of the property mapper module

Finally, we use the PostgreSQL Insert module to write to CrateDB. The primary configuration items here are the CrateDB connection credentials and the target table name:

This already concludes the workflow setup and we are ready to run it.

Heading over to CrateDB’s Admin UI we can now see data arriving:

Raw data in CrateDB

Analytical queries can be used for further analysis, such as grouping by day and aggregating values:

Example of an analytical query in CrateDB

Summary

Crosser can connect to CrateDB Cloud using a standard PostgreSQL connector. Following Crosser’s low-code approach, we can build an end-to-end workflow with only three standard modules. A simple CREATE TABLE statement in CrateDB is enough to persist data and enable real-time insights as well as advanced analytics on longer time intervals.