ClickHouse

ClickHouse is a column-oriented database management system for online analytical processing. It runs analytical queries over large volumes of data in real time. ClickHouse is available as the managed ClickHouse Cloud service or as a self-hosted server.

You can use ClickHouse in Import, Export, and Query, and as a target for Replication.

Establishing Connection

To create a connection to ClickHouse, specify the server host, the user name and password to log in with, and the database name.

Getting Credentials

For ClickHouse Cloud, obtain the connection details in the service console:

  1. Open the ClickHouse Cloud console and select your service.
  2. Click Connect.
  3. Copy the host name and the user name. The default user name is default.
  4. Copy the password set when the service was created. If you lost the password, reset it in the same panel.

For a self-hosted server, use an existing ClickHouse user name and password.

Creating Connection

ClickHouse connection

Specify the following parameters:

  • Connection Mode — whether Skyvia connects to the server directly or through a Skyvia Agent.
  • Host — the ClickHouse server address. In Direct connection mode, enter the host as it appears in the console. In Agent connection mode, enter the host as it appears to the agent machine.
  • User — the user name to log in with.
  • Password — the password to log in with.
  • Database — the ClickHouse database to connect to. Skyvia loads the list of databases from the server after you enter Host, User, and Password.

If Host does not include http:// or https://, Skyvia uses HTTPS. If you do not specify a port, Skyvia uses the default ClickHouse HTTP interface port: 8443 for HTTPS or 8123 for HTTP.

Bulk Load Mode

Bulk Load Mode determines where Skyvia stages temporary files when it loads data in bulk.

Option Description
Streaming Skyvia streams data to the server without intermediate storage.
Amazon S3 Skyvia stages data in Amazon S3, and the server reads it from there. Specify AWS Access Key ID, AWS Secret Key, AWS Security Token, S3 Bucket Region and S3 Bucket Name.
Azure Blob Storage Skyvia stages data in Azure Blob Storage, and the server reads it from there. Specify Azure Storage Account, Azure Storage Account Key, and Azure Storage Endpoints Protocol.

When you select Amazon S3 or Azure Blob Storage and connect through an agent, both the agent machine and the ClickHouse server need access to the storage.

Advanced Settings

Command Timeout

The time in seconds to wait before terminating a command and returning an error. The default value is 3600. Increase it when heavy queries take too long and cause timeout errors.

A ClickHouse Cloud service goes to sleep when you do not use it. The first request after that wakes the service and takes longer than usual, so a short Command Timeout can cause a timeout error.

Role

The role applied to the connection. Skyvia loads the list of roles from the server. An empty list is normal when the user has no roles granted.

Use Bulk Import

When selected, Skyvia loads data in batches. Clear the check box to run per-record statements instead, which provides a per-record error log but works much slower. This option affects only import integrations with the INSERT, UPDATE, and DELETE operations and with ClickHouse as a target.

Connector Specifics

Skyvia has the following limitations for ClickHouse:

  • ClickHouse can only be used as a target in Replication. You cannot replicate data from ClickHouse.
  • History Mode is not supported for replication.
  • You cannot unwind nested objects into separate tables. Skyvia stores nested object arrays as JSON text in a column of the target table.
  • Synchronization is not supported for ClickHouse.

Three ClickHouse features affect how you plan an integration:

  • UPDATE runs row by row. Skyvia updates records one at a time, even when Use Bulk Import is selected, so updating a large number of records takes a long time. Reload the data instead of updating it when the change set is large.
  • A primary key does not enforce uniqueness. ClickHouse does not reject duplicate records. If an integration inserts the same record twice, the target table gets two rows.
  • UPDATE and DELETE run as asynchronous mutations. ClickHouse queues mutations per table and runs them in the background. A run can stay on this step for a long time, and a mutation that cannot finish blocks the ones after it. Canceling the request does not stop a mutation, so avoid short Command Timeout values.

Supported Actions

ClickHouse connector supports the following actions:

On This Page