CH2OTEL - ClickHouse System Metrics to OpenTelemetry Converter

Automatically convert ClickHouse Cloud system metrics and logs into OpenTelemetry standard format using Refreshable Materialized Views (RMV).

🎯 Purpose

CH2OTEL provides seamless integration between ClickHouse Cloud and OTEL-compatible observability tools: - Automatic Conversion - Transform system metrics to OTEL format - Standards Compliant - Full OpenTelemetry Logs, Traces, Metrics support - Self-Service - No collector required, runs entirely within CHC - Secure - Sensitive information managed separately

📁 File Structure

ch2otel/
├── README.md                      # This file
├── setup-ch2otel.sh              # Interactive setup script
├── .credentials                   # Auth info (Git excluded, auto-generated)
├── ch2otel.conf                  # Configuration (Git excluded, auto-generated)
├── scripts/
│   ├── status.sh                 # Check RMV and table status
│   └── refresh.sh                # Manually refresh all RMVs
└── sql/
    └── ch2otel-template.sql      # SQL template for deployment

🚀 Quick Start

cd /path/to/ch2otel
./setup-ch2otel.sh

The interactive setup will guide you through: 1. ClickHouse Cloud connection (host, password) 2. Database configuration (default: ch2otel) 3. Collection settings (refresh interval: 10 min) 4. Data retention (default: 30 days)

📖 Key Features

1. System Log Collection

Collects logs from ClickHouse system tables and converts to OTEL format:

• Part Event Logs (system.part_log)
• NewPart, MergeParts events

• Partition creation and merge monitoring

• Materialized View Execution Logs (system.query_views_log)

• MView execution status tracking

• Performance and error monitoring

• RMV Status Logs (system.view_refreshes)

• Refreshable MView status monitoring
• Refresh schedule and error tracking

2. OTEL Standard Tables

• otel_logs - OTEL standard logs
• otel_traces - OTEL standard traces (v1.1 planned)
• otel_metrics_gauge - Gauge metrics (v1.1 planned)
• otel_metrics_sum - Sum metrics (v1.1 planned)
• otel_metrics_histogram - Histogram metrics (v1.1 planned)
• hyperdx_sessions - HyperDX session data (v1.1 planned)

3. Automatic Data Management

• TTL Policy: Auto-deletion after retention period (default: 30 days)
• Partitioning: Daily partitions for efficient data management
• Compression: ZSTD compression for storage cost reduction

🔍 Usage

Check Status

./scripts/status.sh

Manual Refresh

./scripts/refresh.sh

Query Data

-- Check recent logs
SELECT * FROM ch2otel.otel_logs
ORDER BY Timestamp DESC
LIMIT 10;

-- Check error logs only
SELECT * FROM ch2otel.otel_logs
WHERE SeverityText = 'ERROR'
ORDER BY Timestamp DESC
LIMIT 10;

⚙️ Configuration Changes

Change Refresh Interval

Edit ch2otel.conf:

REFRESH_INTERVAL_MINUTES=5  # Change from 10 to 5 minutes

Then regenerate and execute:

./setup-ch2otel.sh

🛠️ Troubleshooting

RMVs Not Running

SELECT view, status, exception
FROM system.view_refreshes
WHERE database = 'ch2otel';

Connection Error

source .credentials
clickhouse-client --host=$CH_HOST --user=$CH_USER --password=$CH_PASSWORD --secure --query="SELECT version()"

⚠️ Limitations

• Self-Service Only: Monitors current service only (other org services not supported)
• CHC Only: Works only in ClickHouse Cloud environment

🗺️ Roadmap

v1.1 (Planned)

• [ ] Traces RMV (rmv_pipeline_traces)
• [ ] Metrics RMVs (gauge, sum, histogram)
• [ ] Sessions RMV (rmv_pipeline_sessions)

v2.0 (Planned)

• [ ] Collector-based implementation (multi-service support)
• [ ] Multi-service monitoring
• [ ] Alert functionality

📚 References

• OpenTelemetry Specification
• ClickHouse Refreshable Materialized Views
• ClickHouse System Tables