ClickHouse Deployment Guide

Option 1: ClickHouse Cloud

Installation

1. Sign up at https://clickhouse.com/

2. Click Create new Service

1. Fill in the desired details for your ClickHouse server, choosing a name cloud provider and preferred region. Production is recommended. Scaling and idling is up to the demands of your application. When done, click Create Service.

Finding the DSN

CloudQuery expects the ClickHouse DSN in the following format:

clickhouse://<user>:<password>@<host>:9440/<db>?secure

To find the DSN in ClickHouse Cloud:

1. In ClickHouse Cloud Console, select your service.
2. In the left sidebar, click Connect.

3. Select Native and copy the host and password from the dialog box:

1. To connect to the a specific database, add the required database at the end of the connection string - or leave blank to connect to the defaultdatabase. The clickhouse connection string should now look like this:

clickhouse://default:<password>@abcde1web2.us-east-1.aws.clickhouse.cloud:9440/<db>?secure

1. (Optional) If you have the ClickHouse client installed, the DSN can by test by establishing a connection to your instance:

clickhouse client <clickhouse_dsn>

Option 2: Self-Hosted (AWS)

CloudQuery publishes a ClickHouse terraform module to make it easier to deploy a self hosted version of ClickHouse to your AWS account.

For this example, the deployment includes 3 shards (for horizontal scaling) with 2 replicas each (for high availability), secured with TLS, and fronted by a Network Load Balancer (NLB).

Prerequisites

• Terraform v1.10.5 and greater
• AWS Cli v2.x and appropriate credentials
• A domain name for the cluster (e.g. clickhouse.example.com)

• An SSL certificate in AWS Certificate Manager (ACM) with the cluster domain as a valid common name (CN) or subject alternative domain (SAN)

Installation

1. Create a main.tfwith the content, below, replacing the region, cluster_domain and the tls_certificate_arn. The main.tf has some basic configurations included, and you can find the full set in the module docs.

module "clickhouse" {
  source = "github.com/cloudquery/terraform-cloudquery-modules//clickhouse"

  region = "us-east-1"

  # Enable encryption features
  enable_encryption = true
  enable_nlb        = true
  enable_nlb_tls    = true
  nlb_type          = "external"

  # Domain configuration
  cluster_domain = "<cluster_domain>" # e.g. clickhouse.mydomain.com

  # If you have an existing ACM certificate
  tls_certificate_arn = "<tls_certificate_arn>"

  # SSL certificate configuration (for node-to-node communication)
  ssl_cert_days = 365
  ssl_key_bits  = 4096

  # Cluster configuration
  cluster_name       = "myclickhouse"

  # 1 shards with 2 replicas - recommended for initial setups
  shards = [
    {
      replica_count = 2
      weight        = 1
    }
  ]

  # Instance types
  clickhouse_instance_type = "r5.xlarge"
  keeper_instance_type     = "t3.large"

  # Storage configuration
  clickhouse_volume_size = 100
  clickhouse_volume_type = "gp3"
  keeper_volume_size     = 20
  keeper_volume_type     = "gp3"

  # Additional optional settings
  tags = {
    Environment = "production"
    Project     = "data-warehouse"
    Security    = "encrypted"
  }
}

output "clickhouse_nlb_dns" {
   value = module.clickhouse.clickhouse_nlb_dns
}

output "clickhouse_credentials_arn" {
   value = module.clickhouse.clickhouse_credentials_arn
}

1. Run terraform init to initialize the Terraform stack.

2. Run terraform plan to preview the stack to be deployed.

3. Run terraform apply to deploy the stack.

4. Retrieve the database credentials using the clickhouse_credentials_arn from the Terraform output:

aws secretsmanager get-secret-value \
  --secret-id <clickhouse_credentials_arn> \
  --region <region>

1. The clickhouse connection string should look like the following, where the password is retrieved from the AWS secrets manager:

clickhouse://default:<password>@<cluster_domain>:9440/<db>?secure

1. Connect to the ClickHouse instance using the clickhouse client:

clickhouse client <clickhouse_dsn>

1. Create the assets database for the CloudQuery platform to populate e.g.

create database assets on cluster <cluster-name>;