ClickHouse

Overview

HockeyStack's ClickHouse integration enables you to seamlessly connect your ClickHouse instance to our platform. This custom-built connector is designed to import data from ClickHouse based on your specific infrastructure and analytics requirements. Typical use cases include:

• Enriching your analytics with specific user profile or behavioral data

• Mapping offline revenue opportunities to your CRM activities

• Incorporating additional custom data points to unify your customer data story

• Ingesting large volumes of historical data to enhance your analytics

If you're uncertain about which data is most valuable to import, we're happy to schedule a call to help you determine which tables and fields will best support your use cases.

Prerequisites

Before we begin, please ensure you have:

• Access to your ClickHouse instance with the ability to manage users, permissions, and network policies

• Clear information on which ClickHouse tables, databases, and columns contain the data you'd like HockeyStack to ingest

• An understanding of whether HockeyStack should have read-only access, or if write permissions are also needed

Connecting to ClickHouse

To establish a secure connection between HockeyStack and ClickHouse, follow these steps:

1. IP Whitelisting (if applicable)

If you have IP restrictions in place, whitelist the following IP addresses to allow HockeyStack's workers to communicate with ClickHouse:

• `18.184.228.143`

• `18.192.106.69`

• `35.157.54.242`

• `3.69.98.171`

2. Assigning Permissions

Decide whether HockeyStack should have read-only or read-write permissions, depending on your use case. Create a user with access to the necessary databases and tables.

For read-only access:

CREATE USER hockeystack_user IDENTIFIED BY 'your_secure_password';

GRANT SELECT ON your_database.* TO hockeystack_user;

For read-write access:

CREATE USER hockeystack_user IDENTIFIED BY 'your_secure_password';

GRANT SELECT, INSERT, ALTER, CREATE ON your_database.* TO hockeystack_user;

Additional Resources: ClickHouse Access Rights Documentation, Managing Users and Roles

3. Connection Details

Once you've configured the user and permissions, provide us with the following details using a secure method of your choice. Popular choices are:

• One Time Secret: https://onetimesecret.com or

• 1Password: https://1password.com

Field	Description	Examp
Host	Your ClickHouse server hostname or IP	clickhouse.yourcompany.com
Port	Connection port (default: `8123` for HTTP, `8443` for HTTPS) Note: Port 8443 will automatically enable HTTPS connections for secure data transfer.	8123
Database	The database containing your data	analytics
Username	The user created for HockeyStack	hockeystack_user
Password	The password for the HockeyStack user	(securely shared)
Table	The specific table to import data from	events

4. Integration Details

We connect using Node.js workers with the official ClickHouse client to ensure a stable and efficient integration. For more technical details on how the connector works, refer to ClickHouse's official documentation: https://clickhouse.com/docs/en/integrations/language-clients/javascript)

Pulling Data from ClickHouse

HockeyStack runs scheduled workers (typically daily, though this can be customized) to pull data from your ClickHouse instance. The process is as follows:

Initial Historical Backfill

We start by running a full backfill of your historical data. This ensures that HockeyStack has all necessary historical records before moving on to incremental updates. Depending on the size of your dataset, this process may take some time.

Incremental Data Syncs

After the initial backfill, we perform incremental data syncs at the scheduled frequency. These syncs pull in new or updated records since the last sync, ensuring your HockeyStack analytics remain up-to-date.To enable incremental syncs, your table should have a timestamp column (such as `updated_at` or `created_at`) that we can use to identify new or modified records.

Troubleshooting

If you encounter connection issues, please verify:

1. The ClickHouse server is accessible from HockeyStack's IP addresses

2. The user has the correct permissions on the specified database and table

3. The connection details (host, port, credentials) are accurate

4. Any firewall rules allow incoming connections on the specified port

Support

If you have any questions or need assistance during the integration process, please reach out to our support team at [email protected].