ClickHouse

ClickHouse® is a high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP).

ClickPipes supports the creation of data pipelines with ClickHouse as the target database. The following article provides a comprehensive guide on how to add ClickHouse to ClickPipes.

Supported Versions and Architectures

Category	Description
Version	ClickHouse v21.x and above
Architecture	Supports both standalone and cluster deployments

Supported Data Types

Category	Data Types
Numeric Types	Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64, Float32, Float64, Decimal
String Types	String, FixedString
Date & Time Types	Date, DateTime, DateTime64
Boolean Type	Bool
Array & Tuple Types	Array, Tuple
Special Types	UUID, Enum, Nullable

Precautions

If binary-related fields are included, you need to remove them via field mapping for data synchronization/development.

Preparations

1. Adjust the configuration file user.xml, enable access control and restart the service. For more information, see Enable Access Control.

   tip

   You can also use this file to modify the account configuration, this article demonstrates how to create and authorize an account after the permission control is turned on.

2. Log in to the ClickHouse database and execute the following commands to create an account for data synchronization/development tasks.

   CREATE USER username HOST 'host' IDENTIFIED WITH protection BY 'password';
   • username: Enter user name.
   • host: Which host can be accessed by the account, any means to allow all host.
   • protection: Password protection.
   • password: Enter password.

   Example: Create an account named clickpipes , using the sha256_password protection mechanism, allowing it to log in from any host.

   CREATE USER clickpipes HOST ANY IDENTIFIED WITH sha256_password BY 'clickpipes@123456';

3. To grant permissions to the account you have just created, it is advisable to implement more granular permission controls based on your business needs. For detailed instructions on authorization syntax and further information, see authorization syntax.

   GRANT SELECT, INSERT, CREATE TABLE, ALTER TABLE, ALTER UPDATE, DROP TABLE, TRUNCATE ON database_name.* TO username
   • database_name: Enter database name.
   • username: Enter user name.

Source Config

• Connection Settings

  • Connection name: Fill in a unique name that has business significance.
  • Connection type: ClickHouse databases can only be targets.
  • Host: The database connection address.
  • Port: The HTTP API service port of the database, the default is 8123. If SSL encryption is enabled, the default port is 8443. For more information, see network ports.
  • Database: Database name, a connection corresponding to a database, if there are multiple databases, you need to create multiple connections.
  • Username, Password: The database username and password.
  • Connection parameter string: Additional connection parameters, default empty.

• Advanced Settings

  • Timezone: Defaults to the time zone used by the database, which you can also manually specify according to your business needs.
  • Agent settings: Defaults to Platform automatic allocation, you can also manually specify an agent.
  • Model load time: If there are less than 10,000 models in the data source, their information will be updated every hour. But if the number of models exceeds 10,000, the refresh will take place daily at the time you have specified.