​
Overview

The OneSignal + ClickHouse integration enables automatic syncing of custom events from your ClickHouse analytics database to OneSignal. This allows you to trigger automated Journeys and personalized messaging campaigns based on user behavioral data stored in your high-performance columnar database.

​
Requirements

​
ClickHouse

  • ClickHouse server (self-hosted or cloud)
  • Database credentials with read access to event tables
  • Event data tables containing behavioral data with proper schema

​
Setup

1

Create ClickHouse user for OneSignal

Create a dedicated user account for OneSignal with read-only access to your event tables:
CREATE USER onesignal_reader IDENTIFIED BY 'strong_password';
GRANT SELECT ON event_database.* TO onesignal_reader;
2

Configure network access

Ensure OneSignal can connect to your ClickHouse instance:
  • Self-hosted: Allow connections from OneSignal’s IP addresses
  • ClickHouse Cloud: Add OneSignal IPs to your allowlist
  • Port: Default ClickHouse port is 8123 (HTTP) or 9000 (native)
3

Add integration in OneSignal

In OneSignal, go to Data > Integrations and click Add Integration.Select ClickHouse and provide:
  • Host: Your ClickHouse server hostname or IP
  • Port: ClickHouse port (default: 8123 for HTTP, 9000 for native)
  • Database: Database name containing event tables
  • Username: onesignal_reader (or your chosen username)
  • Password: Password for the ClickHouse user
  • Protocol: HTTP or Native (HTTP recommended for simplicity)
4

Configure event data source

Specify the ClickHouse table containing your event data:
  • Table: Table name containing event records (e.g., user_events)
  • Event Query: Optional SQL query to filter or transform event data
Your event table should contain columns for:
  • Event name/type (String)
  • User identifier (String)
  • Event timestamp (DateTime)
  • Additional event properties (JSON or individual columns)
5

Test the connection

Click Test Connection to verify OneSignal can access your ClickHouse database and read event data.

​
Event data mapping

Map your to OneSignal’s custom events format:
OneSignal FieldDescriptionRequired
nameevent_nameEvent identifierYes
external_iduser_idUser identifierYes
timestampevent_timestampWhen event occurredNo
propertiesevent_dataNo

​
Advanced Configuration

​
Custom SQL Queries

Use custom SQL to filter or transform event data before syncing to OneSignal: 
SELECT
  event_name,
  user_id,
  toDateTime(event_timestamp) as timestamp,
  toJSONString(
    map(
      'product_id', product_id,
      'purchase_amount', purchase_amount,
      'category', category
    )
  ) as payload
FROM user_events
WHERE event_timestamp >= now() - INTERVAL 7 DAY
  AND event_name IN ('purchase', 'signup', 'upgrade')
ORDER BY event_timestamp DESC

​
Performance Optimization

ClickHouse is optimized for analytical queries. Consider:
  • Partitioning: Use date-based partitioning on event timestamp
  • Indexing: Create appropriate indexes on user_id and event_name
  • Materialized Views: Pre-aggregate event data for faster querying
ClickHouse is optimized for append-only workloads. Ensure your event data follows this pattern for best performance.

​
FAQ

​
How often does OneSignal sync events from ClickHouse?

OneSignal syncs event data based on your configured schedule, with a minimum interval of 15 minutes.

​
Can I sync events from multiple ClickHouse tables?

Yes, you can create multiple integrations for different tables or use UNION queries to combine data from multiple tables.