Greenplum

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

Overview

The OneSignal + Greenplum integration enables syncing of custom events from your Greenplum database to OneSignal to trigger automated messaging campaigns and Journeys based on user behavior.

Greenplum is a massively parallel processing (MPP) database built on PostgreSQL, designed for large-scale analytics workloads.

---

Requirements

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

Greenplum

• Greenplum instance with network access
• Database user with appropriate permissions
• Event tables containing structured behavioral data

---

Sync Engines and Permissions

OneSignal reads data from tables and views in Greenplum and syncs it to trigger automated messaging campaigns. To limit the load on your database, OneSignal maintains state tracking tables that enable it to only sync data that has been modified since the last sync (incremental syncs). When configuring your Greenplum connection, you’ll choose a Sync Engine that determines how state tracking is handled.

The Basic Sync Engine maintains state tracking tables on OneSignal-owned infrastructure and is simpler to configure, requiring read access only.

The Advanced Sync Engine delivers enhanced performance by maintaining state tracking tables in a dedicated schema within your own Greenplum instance.

Setup

1. Create a Census user

   Create a dedicated database user for OneSignal to use:

   -- Create CENSUS user and set password
   CREATE USER CENSUS WITH PASSWORD '<strong unique password>';
   

2. Choose your sync engine and configure permissions

   For Basic Sync Engine (Read-only access):

   Grant read access to your event data schema. Replace <your schema> with your schema name:

   -- Let the census user read all existing tables in this schema
   GRANT SELECT ON ALL TABLES IN SCHEMA "<your schema>" TO CENSUS;
   
   -- Let the census user read any new tables added to this schema
   ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT SELECT ON TABLES TO CENSUS;
   
   -- Let the census user execute any existing functions in this schema
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "<your schema>" TO CENSUS;
   
   -- Let the census user execute any new functions added to this schema
   ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT EXECUTE ON FUNCTIONS TO CENSUS;
   

   For Advanced Sync Engine (Enhanced performance):

   First complete the Basic Sync Engine steps above, then add:

   -- Create a private bookkeeping schema where Census can store sync state
   CREATE SCHEMA CENSUS;
   
   -- Give the census user full access to the bookkeeping schema
   GRANT ALL ON SCHEMA CENSUS TO CENSUS;
   
   -- Ensure the census user has access to any existing objects in the bookkeeping schema
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA CENSUS TO CENSUS;
   
   -- Let the census user see your data schema
   GRANT USAGE ON SCHEMA "<your schema>" TO CENSUS;
   

3. Connect to OneSignal

   In OneSignal, go to Data > Integrations and click Add Integration.

   Select Greenplum and provide:

   • Host: Your Greenplum master host
   • Port: 5432 (or custom port)
   • Database: Your database name
   • Username: CENSUS
   • Password: Password from Step 1
   • Sync Engine: Choose Basic or Advanced based on Step 2

---

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

Example Event Table Schema

-- Example Greenplum event table
CREATE TABLE analytics.user_events (
    event_id BIGSERIAL,
    event_name VARCHAR(100) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    event_properties JSONB,
    session_id VARCHAR(255),
    device_type VARCHAR(50)
);

---

Processing Modes

Table Mode

Sync entire tables or views directly from your Greenplum database. OneSignal will automatically map columns to event fields.

SQL Query Mode

Write custom PostgreSQL-compatible queries to transform your event data:

-- Example: Recent high-value events
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties
FROM analytics.user_events
WHERE event_timestamp >= NOW() - INTERVAL '7 days'
    AND (event_properties->>'value')::NUMERIC > 100
ORDER BY event_timestamp DESC;

MPP Query Optimization

Take advantage of Greenplum’s parallel processing by ensuring your event queries are optimized for distributed execution. Use appropriate distribution keys and avoid cross-segment data movement for better performance.

---

Advanced Network Configuration

OneSignal can successfully connect to Greenplum instances that are using advanced networking controls including region constraints, IP address allow lists, or SSH Tunneling.

We recommend configuring your Greenplum instance to use TLS v1.2 or later for all connections.

Limitations

• Large analytical queries may impact cluster performance
• JSON/JSONB operations should be optimized for distribution
• Cross-segment joins should be minimized for performance

---

FAQ

Which sync engine should I choose?

Use the Basic Sync Engine if you prefer simpler setup and read-only access. Choose the Advanced Sync Engine if you need enhanced performance and can allow OneSignal to create tables in your Greenplum instance.

How do I optimize queries for Greenplum’s MPP architecture?

Ensure queries utilize distribution keys effectively, avoid unnecessary data movement between segments, and leverage Greenplum’s columnar storage for analytics.

Can I use Greenplum’s external tables for event data?

Yes, OneSignal can read from external tables that reference data in formats like Parquet or CSV stored in external systems.

---