ClickHouse

Sync custom events from ClickHouse to OneSignal to trigger automated Journeys and personalized messaging campaigns based on user behavior.

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

• Access to Custom Events (currently in beta)
• Updated Account Plan (not available on free apps).

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 Field	Description	Required
name	event_name	Event identifier	Yes
external_id	user_id	User identifier	Yes
timestamp	event_timestamp	When event occurred	No
properties	event_data	No

---

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.