- Calculated Property
- Google Sheets
ClickHouseLast Updated: 2022-01-26
Grouparoo's ClickHouse Integration is configurable as both a Source and a Destination that enables you to import and export customer data to and from one or more ClickHouse databases. ClickHouse is a relational database management system (RDBMS). You can use Grouparoo to sync data between a ClickHouse database and other services to operationalize your data.
|Clickhouse Import Table||Import or update Records from a ClickHouse database table.|
|Clickhouse Import Query||Import or update Records via a custom ClickHouse query.|
|Clickhouse Export Record||Export Records to a ClickHouse table. Groups will be exported to a secondary table linked by a foreign key.|
To work with the ClickHouse Plugin, you must first install it in an existing Grouparoo project. Grouparoo supports syncing with ClickHouse databases stored on a local machine or remotely, provided that there is no firewall or VPN prohibiting the connection. How you connect depends on how you configure the App.
To connect ClickHouse to Grouparoo, you create an App to tell Grouparoo how to connect to ClickHouse. This specifies the connection details for the database you want to use while syncing data.
In Grouparoo, provide the options that best describe how you would like to connect to ClickHouse.
|Host||The ClickHouse HTTP host.|
|Port||The ClickHouse HTTP interface port. Default value of 8123 can be changed manually.|
|Database (required)||The name of the ClickHouse database.|
|User||The name of the Clickhouse user.|
|Password||The ClickHouse user's password.|
This source reads from a single schema or table in your ClickHouse database using a primary or foreign key. Properties are generated from this table from row values or aggregations of row values.
There is one option to set for a ClickHouse Table Import Source. You need to choose the table from your database that will serve as the location for Grouparoo to scan for changes to Properties.
|Table (required)||The table you want to scan for updates to your data.|
Defining Mappings is a critical part of the process. It tells Grouparoo which Record to associate with the imported data.
If you are using ClickHouse Import Table as the primary source, choose the database column that uniquely identifies Records in your Model. This will often be the primary key of the table that represents your Model.
In secondary sources, choose the database column that ties this table to your primary source. This will often be a foreign key in your table.
Create a schedule to actively pull new data from this ClickHouse table. If the recurring option is not selected, you are creating a one-time sync. There are also options to confirm records exist when running a schedule and to enable refresh. When Recurring is selected, you will see a field for entering the frequency, in minutes, of recurrence.
Configure a schedule for your project and determine how Grouparoo should check for new data by filling out the options for this Source.
|column (required)||Select which column Grouparoo should check to look for new data. Often, this would be a column like “updated_at” - Grouparoo remembers the most recent values so it can be based on an incremental count as well.|
If there is a subset of rows' data you want to specifically include or exclude from your ClickHouse database table, create a Filter.
Properties populate your Records with data from your Clickhouse table based on table columns. Create a Property to be able to use row data in Grouparoo.
|Column Name (required)||Choose the column that will be the source of data for this Grouparoo Property.|
|Aggregation Method (required)||Aggregation methods determine how to use the row values in the table. ClickHouse supports the standard set of aggregation methods for table sources.|
If there is a subset of rows data you want to specifically include or exclude from your ClickHouse database table, create a Filter.
A Query Source works differently than a table-based source in that there are no options to choose in setting up the connection to this type of source. A Query Source is a more flexible way to build properties.
This type of Source provides the ability to write custom SQL code to extract data from one or more tables and import the result into Grouparoo.
ClickHouse Export Record allows you to set up a Grouparoo Project that uses ClickHouse as a Destination. As with ClickHouse Sources, to set up ClickHouse as a Destination you will need to have the App installed and configured with connection details.
A Destination’s sync mode determines what to update in a Destination. ClickHouse supports the default sync behavior.
- Sync: Add, update, and remove ClickHouse Exported Properties as needed. This option looks for new records, changes to existing records, and deletions.
To further clarify how ClickHouse should sync data, select from the options provided that are specific to your database.
|Table (Required)||The name of the table used to export Records. You must choose a table that already exists in the ClickHouse database you are syncing to.|
|Primary Key (Required)||The primary key of the table. This will be used in the mapping of properties.|
|Groups Table (Required)||The name of the table used to export groups. This table must already exist in your database and must use a foreign key to relate to the primary key you have already designated for in the previous field.|
|Group Foreign Key (Required)||The column name for the foreign key that the groups table uses to reference the records table.|
|Group Column Name (Required)||The column name used to store group names.|
In a ClickHouse Export Contacts Destination, you must choose the Grouparoo Property to map to the column listed as the primary key in the ClickHouse database table that this Destination syncs to. Therefore, the Exported Property listed as required will be whatever is designated as the Primary Key in the ClickHouse table.
In addition to the required mapping for your table’s primary key, any other columns in the table can also have mappings to Grouparoo properties.
You can also use Grouparoo Groups to assign records to a Group in ClickHouse. This information is recorded in a table that lists the Grouping alongside a foreign key that relates to the associated Record being assigned to that Group in the ClickHouse database.
We strive to keep our documentation accurate, easy to use, and up to date. If you notice that something has changed or is not addressed in the process of setting up your Grouparoo connection with
ClickHouse, please let us know by reaching out to email@example.com.
As always, if you need support using Grouparoo, reach out to community members in our Slack channel. We are always happy to help!Join us on Slack
- Calculated Property
- Google Sheets