​
Introduction

This guide explains how to integrate Proxus with an existing ClickHouse instance, a high-performance columnar database optimized for real-time analytics. By connecting Proxus to ClickHouse, you can store and analyze device data at scale, enabling actionable insights and robust monitoring.

​
What is ClickHouse?

ClickHouse is an open-source columnar database management system designed for online analytical processing (OLAP). It excels at handling large volumes of data with low-latency queries, making it ideal for time-series and real-time analytics.

​
Why Integrate Proxus with ClickHouse?

Integrating Proxus with ClickHouse offers several benefits:

  • High-Performance Analytics: Process massive datasets with sub-second query times.

  • Real-Time Data Ingestion: Stream device data for immediate analysis.

  • Scalability: Handle growing data volumes effortlessly.

  • Cost Efficiency: Leverage an open-source solution for enterprise-grade analytics.

​
Preparing ClickHouse

This guide assumes you already have a running ClickHouse instance. Ensure it’s configured to accept connections from Proxus.

1

Verify Connectivity

Test connectivity to your ClickHouse server using the HTTP interface (e.g., http://<clickhouse-host>:8123/ping). It should return OK.

2

Check Credentials

Confirm you have the correct username and password for ClickHouse access (e.g., admin/clickhouse123).

​
Configuring Proxus

​
Defining a ClickHouse Outbound Channel

1

Log in to Proxus

Log in to the Proxus web interface.

2

Navigate to Outbound Channels

Go to Integrations > Outbound Channels.

3

Create New Channel

Click + New to add a new outbound channel.

4

Fill in Channel Details

Provide the following details:

  • Target Type: ClickHouse
  • Profile Name: ClickHouseAnalytics (e.g.)
  • Description: ClickHouse Integration for Device Data
  • Transport Strategy: Pass Through Strategy

​
Adding ClickHouse Parameters

1

Go to Parameters Tab

Navigate to the Parameters tab in the channel configuration.

2

Add Key-Value Pairs

Add these parameters to connect to your ClickHouse instance:

  • Key: Host, Value: <clickhouse-host> (e.g., localhost)
  • Key: Port, Value: 8123 (or your ClickHouse HTTP port)
  • Key: Username, Value: admin (or your username)
  • Key: Password, Value: clickhouse123 (or your password)
  • Key: Database, Value: proxus (default or your database)
  • Key: Table, Value: DeviceRawData (default or your table)
  • Key: WriteIntervalSeconds, Value: 5 (buffer every 5 seconds)
  • Key: TTLExpression, Value: See examples below (optional)
3

Save Configuration

Click Save to apply the settings.

​
TTL Expression Examples

The TTLExpression parameter defines how long data remains in ClickHouse before automatic deletion. Use ClickHouse’s TTL syntax based on the Time column. Here are some examples:

​
Sending Data to ClickHouse

​
Linking ClickHouse Profile to a Device

1

Navigate to Devices

Go to Data Management > Devices in Proxus.

2

Select Device

Choose the device to send data from.

3

Go to Target Profiles

In the device details, navigate to Target Profiles.

4

Add ClickHouse Profile

Add the profile:

  • Profile Name: ClickHouseAnalytics
  • Target Type: ClickHouse
  • Transport Strategy: Pass Through Strategy

​
Verifying Data Transmission

1

Activate Device

Ensure the device’s Status is “Active.”

2

Check Logs

Verify Proxus logs for messages like [ClickHouse] Added X records to buffer and [ClickHouse] Flushed Y records.

​
Querying Data in ClickHouse

​
Accessing ClickHouse

Connect to your ClickHouse instance using a client (e.g., clickhouse-client) or the HTTP interface (e.g., http://<clickhouse-host>:8123).

​
Basic Queries

1

Verify Data

Run this query to see recent data:

SELECT * FROM proxus.DeviceRawData ORDER BY Time DESC LIMIT 10;
2

Aggregate Data

Calculate average values by device:

SELECT DeviceName, AVG(NumericValue) as AvgValue
FROM proxus.DeviceRawData
GROUP BY DeviceName;

​
Data Structure

The DeviceRawData table in ClickHouse has the following schema:

ColumnTypeDescriptionExample
TimeDateTime64(3)Timestamp of data collection2025-02-25 09:36:00.000
DeviceIdUInt32Unique device identifier1
DeviceNameStringName of the deviceSensor1
KeyStringData key (e.g., temperature)temperature
DataTypeEnum8Type of data (e.g., Double, String)8 (Double)
NumericValueFloat64Numeric value of the data25.5
StringValueStringString value (if applicable)"" or "High"

​
Optimizing Performance

​
Asynchronous Inserts

The integration uses ClickHouse’s asynchronous insert feature to buffer data, reducing disk writes. Proxus buffers data client-side every 5 seconds (WriteIntervalSeconds=5), which aligns with server-side settings if configured (e.g., async_insert_busy_timeout_ms=5000).

​
Monitoring Parts

Check active parts to ensure efficient data ingestion:

SELECT table, name, rows, bytes_on_disk
FROM system.parts
WHERE table = 'DeviceRawData' AND active = 1
ORDER BY modification_time DESC;

​
Troubleshooting

​
Common Issues

​
Best Practices

  • Buffer Tuning: Set WriteIntervalSeconds based on data volume (e.g., 5-10 seconds for high throughput).

  • Retention Management: Use TTLExpression to control data lifecycle. Examples:

    • Short-term: toDateTime(Time) + INTERVAL 1 WEEK

    • Long-term: toDateTime(Time) + INTERVAL 1 YEAR

  • Monitoring: Regularly query system.parts to optimize part count and storage.

​
Conclusion

This guide has shown you how to integrate Proxus with an existing ClickHouse instance using the ClickHouseIntegrationActor. You can now efficiently store device data, perform real-time analytics, and scale your monitoring solution with ease.