Smart Ingest lets you pull data stored in your ClickHouse database and push it to Iterable. This integration supports ClickHouse v20 and later.
NOTE
Contact your Iterable customer success manager to discuss adding Smart Ingest to your plan.
Smart Ingest can only import data from ClickHouse.
In this article
Connection requirements
Before connecting ClickHouse to Smart Ingest, you need to do the following:
- Allow Smart Ingest IPs in ClickHouse.
- Choose an authentication method and get the required connection details from your ClickHouse administrator.
- Choose a connection type (if using password authentication).
Allowing Smart Ingest IPs in ClickHouse
Before you connect Smart Ingest to ClickHouse, make sure to allow Smart Ingest IPs in your ClickHouse account.
Authentication methods
There are two ways to authenticate with ClickHouse:
- A username and password for ClickHouse.
- A connection string provided by your ClickHouse administrator.
Password authentication
When you authenticate to ClickHouse with a username and password, you can connect directly to your ClickHouse account's server by providing a URL or IP address, or you can use an SSH tunnel to connect.
To connect to ClickHouse with a username and password, you need the following information from your ClickHouse administrator:
Host - The hostname or IP address of your ClickHouse server.
Port - The port number of your ClickHouse server's HTTP API. The default is
8123, but yours may be different.Database - The name of the database to use when Smart Ingest executes queries in ClickHouse.
Username - This can be your personal ClickHouse login, or a dedicated user for Smart Ingest. The user should, at minimum, have permission to read the tables you want to sync.
Password - The password for the user specified above.
Connection string authentication
To connect to ClickHouse using a connection string, ask your data warehouse administrator to provide the JDBC connection string. This string contains all the information Smart Ingest needs to connect to your ClickHouse account, and can be customized to your configuration needs.
Smart Ingest uses the official ClickHouse JDBC driver.
When you use a JDBC connection string, you cannot also use an SSH tunnel.
WARNING
JDBC connection string security
A JDBC connection string contains all the information Smart Ingest needs to connect to your ClickHouse account. This includes the hostname, port, database name, and user credentials, as well as any additional configuration settings.
Because the connection string contains sensitive information, keep it secure and don't share it with others. Once you enter your connection string and click Continue, Smart Ingest obscures the string so it no longer appears in the UI.
If you need to update the connection string, you must re-enter it. The existing string is not visible in the UI for security reasons.
Connection types
Smart Ingest supports two connection types for ClickHouse:
A direct connection over the public internet.
-
An SSH tunnel. This is available for password authentication only.
To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.
The connection type you choose depends on your ClickHouse configuration and your security requirements. Because data is encrypted in transit via TLS, a direct connection is suitable for most use cases. You may need to set up a tunnel if your ClickHouse instance is on a private network or virtual private cloud (VPC).
Data model requirements
When you create a sync, Smart Ingest uses the SQL editor to model your ClickHouse data. The table selector is not supported for ClickHouse, so you must write your own SQL query to pull data from ClickHouse.
Required data fields vary depending on the sync you are creating. To find a listing of fields for mapping, read the guide for the sync type that you want to create.
Connecting ClickHouse with a password
To connect Smart Ingest to ClickHouse with a username and password:
Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.
Go to Integrations > Smart Ingest.
Click Connect a New Source.
Select ClickHouse and click Continue.
In Step 1, select the Password authentication type.
-
In Step 2, select the connection type your sync should use to connect:
Connect Directly to ClickHouse. This creates a direct connection with no additional setup needed.
-
Connect via SSH Tunnel. This connects using SSH. Add a standard or reverse tunnel, or select an existing tunnel if you’ve already set one up.
To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.
In Step 3, enter your ClickHouse account details: Host, Port, and Database.
Next, enter your user credentials for ClickHouse: Username and Password.
Click Continue. Smart Ingest automatically tests the connection.
When the connection test is successful, click Continue. (If there are problems connecting, click Back and review the connection details for accuracy.)
Add a name for your data source to display in Iterable.
Click Finish.
Connecting ClickHouse with a connection string
Once you have a connection string from ClickHouse, you can connect Smart Ingest to ClickHouse.
To connect Smart Ingest with ClickHouse using a connection string:
Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.
Go to Integrations > Smart Ingest.
Click Connect a New Source.
Select ClickHouse and click Continue.
In Step 1, select the Connection String authentication type.
In Step 2, enter your JDBC connection string.
Click Continue. Smart Ingest automatically tests the connection.
-
When the connection test is successful, click Continue.
If there are problems connecting, first try again. If you still can't connect, click Back and re-enter the connection string.
Add a name for your data source to display in Iterable.
Click Finish.
Next steps
You've now connected your data warehouse to Smart Ingest. The next thing you can do is create a sync.