ClickHouse

Overview

Stigg’s integration with ClickHouse allows you to export your product catalog, customer, and subscription data to ClickHouse for high-performance analytical queries.

Stigg’s native data export integrations are included in the Scale plan, and are also available as an optional add-on to the Growth plan. See Stigg’s pricing for more details.

View the full entity schema

See every table and column exported to your destination, organized by entity group.

Setting up the integration

Prerequisites

A ClickHouse instance (self-hosted or ClickHouse Cloud) with administrative access
An S3 or GCS bucket for staging data before it’s loaded into ClickHouse (or use Implicit staging if credentials are already configured on your ClickHouse instance)

If your security posture requires IP allowlisting, Stigg connects from a static IP: 35.192.85.117/32 (US Cloud) or 104.199.49.149/32 (EU Cloud). For private or self-hosted deployments, contact Stigg support for the egress IP.

SSH tunneling isn’t currently supported for ClickHouse destinations.

Allow network access

Whitelist Stigg’s static IP in your firewall or security group:

Incoming connections to your ClickHouse host and port (typically 9440) from Stigg’s static IP
Outgoing connections from ports 1024-65535 to Stigg’s static IP

Create a database user

Connect to your ClickHouse instance with a SQL client and create a user for Stigg:

CREATE USER <username>'%' IDENTIFIED BY '<some-password>';

Passwords may only contain alphanumeric characters, dashes, and underscores.

Grant the permissions Stigg needs to stage and load data:

GRANT SELECT ON information_schema.columns TO <username>;
GRANT CREATE, INSERT, DROP, ALTER, OPTIMIZE, SHOW, TRUNCATE ON <database>.* TO <username>'%';
GRANT CREATE TEMPORARY TABLE, S3 ON *.* TO <username>'%';

Configure the staging bucket

Choose an S3 or GCS bucket for staging, or select Implicit if credentials are already configured as environment variables on your ClickHouse instance. Optionally add a lifecycle rule to delete staged objects after 2 days.

For ClickHouse Cloud using S3 role-based access, add a trust policy on your S3 role allowing ClickHouse’s IAM ARN to assume it:

{
    "Effect": "Allow",
    "Principal": {"AWS": "<CLICKHOUSE_IAM_ARN>"},
    "Action": "sts:AssumeRole"
}

Find the ARN in ClickHouse Cloud under Settings > Network security information > Service role ID.

Connect Stigg with ClickHouse

In Stigg, navigate to Integrations > Apps > ClickHouse.Enter the following information in the connection form:

Field	Description
Host	The hostname of your ClickHouse server
Port	The port for the native protocol. Use `9000` (not the HTTPS port).
Cluster	The ClickHouse cluster name (if using a clustered deployment)
Database	The connection database
Schema	The database where Stigg will write tables (the write database). Can be the same as Database.
Username	The ClickHouse user created above
Password	The password for the ClickHouse user
Staging bucket type	S3, GCS, or Implicit
Staging bucket	The bucket name, region, and credentials used for staging. Not required if Implicit is selected.

Click Test & connect.

Select entities to export

After entering your connection details, expand the Entities to export section to choose which entity groups to include in the sync. All groups are selected by default.See Exported entities for a description of each group.

Querying exported data

Stigg’s tables in ClickHouse use the ReplacingMergeTree engine, which deduplicates rows asynchronously in the background. Use the FINAL keyword to force deduplication at query time:

SELECT * FROM schema.table FINAL WHERE foo = bar;

Getting started

Developer tools

Governance (Private Beta)

Modeling your pricing in Stigg

Entitlements

Managing customers, subscriptions and contracts

Getting usage data into Stigg

Snap-in widgets

Native integrations

Importing and exporting data

Going live

Workflow automation

Rolling out pricing changes with confidence

Price localization

Managing your account

High-availability and scale

Security & compliance

Overview

View the full entity schema

Setting up the integration

Prerequisites

Querying exported data

​Overview

View the full entity schema

​Setting up the integration

​Prerequisites

​Querying exported data

Overview

Setting up the integration

Prerequisites

Querying exported data