Skip to main content

​
CrateDB Overview

The CRATEDB route stores MQTT messages in CrateDB, a distributed SQL database optimized for time-series and machine data. It combines the familiarity of SQL with the scalability needed for IoT workloads.
CrateDB excels at high-volume time-series data with fast ingestion and SQL query capabilities. Use it when you need real-time analytics on sensor data at scale.

​
Basic Syntax

DEFINE ROUTE SensorDB WITH TYPE CRATEDB
    ADD CRATEDB_CONFIG
        WITH HOST "cratedb.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot"
        WITH USERNAME "crate"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO sensor_data (ts, sensor_id, value) VALUES (?, ?, ?)"

​
Connection Configuration

​
CRATEDB_CONFIG Parameters

​
HOST
string
required
CrateDB server hostname or IP address.
​
PORT
integer
CrateDB PostgreSQL protocol port. Default: 5432.
​
DATABASE
string
Target database/schema name.
​
USERNAME
string
required
Database username.
​
PASSWORD
string
required
Database password.

​
Complete Examples

Store sensor readings with timestamps:
DEFINE ROUTE TimeSeriesDB WITH TYPE CRATEDB
    ADD CRATEDB_CONFIG
        WITH HOST "cratedb.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot"
        WITH USERNAME "crate"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreTimeSeries
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH DESTINATION_TOPIC "db/crate/status"
        WITH QUERY "INSERT INTO sensor_data (ts, sensor_id, value) VALUES (?, ?, ?)"

​
Table Schema Examples

Create tables optimized for time-series: 
-- Partitioned time-series table
CREATE TABLE sensor_data (
    ts TIMESTAMP WITH TIME ZONE,
    sensor_id TEXT,
    value DOUBLE PRECISION
) PARTITIONED BY (ts);

-- Metrics with JSON storage
CREATE TABLE metrics (
    ts TIMESTAMP WITH TIME ZONE,
    topic TEXT,
    data OBJECT(DYNAMIC)
) PARTITIONED BY (ts);

​
Troubleshooting

  • Verify HOST and PORT are correct
  • Check CrateDB is running and accepting connections
  • Verify firewall allows connections on port 5432
  • Verify USERNAME and PASSWORD are correct
  • Check user has permissions on the DATABASE
  • Verify table exists and has correct schema
  • Check column types match inserted data

​
Next Steps

Snowflake Route

Configure Snowflake cloud data warehouse.

Storage Overview

Compare all storage options.