ClickHouse

Sign in to the AWS Management Console, open the EC2 service, and choose Launch instance. Under Application and OS Images, choose AWS Marketplace AMIs and search for ClickHouse. Select the SecureAnyCloud listing and choose Select, then Continue on the subscription summary.

Pick an instance type of m5.large or larger. Choose your EC2 key pair under Key pair (login).

Under Network settings, select your VPC and subnet. Create or select a security group that allows inbound port 22 from your management network and inbound ports 8123 and 9000 from the trusted networks that will reach ClickHouse. Do not open the ClickHouse ports to the public internet. The /play SQL playground is served on the HTTP port, and a public SQL endpoint is a serious security exposure. Leave the root volume at the default size or larger.

Choose Launch instance. First-boot initialization takes approximately one minute after the instance state becomes Running and the status checks pass.

The following block launches an instance from the SecureAnyCloud ClickHouse Marketplace AMI into an existing subnet and security group.

Replace <ami-id> with the AMI ID shown on the Marketplace listing, <key-name> with your EC2 key pair name, and <subnet-id> with your subnet ID. Replace <security-group-id> with a security group that opens ports 22, 8123 and 9000 as described above.

aws ec2 run-instances \
  --image-id <ami-id> \
  --instance-type m5.large \
  --key-name <key-name> \
  --subnet-id <subnet-id> \
  --security-group-ids <security-group-id> \
  --block-device-mappings '[{"DeviceName":"/dev/sda1","Ebs":{"VolumeSize":30,"VolumeType":"gp3"}}]' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=clickhouse-01}]'

The command prints a JSON document on success. Note the instance ID. Once the instance is running, retrieve its public address with aws ec2 describe-instances --instance-ids <instance-id> --query "Reservations[].Instances[].PublicIpAddress" --output text.

Connect over SSH with the key pair you selected and the public IP address from Step 2. The SSH login user depends on the operating system of the AMI variant you launched:

AMI variant SSH login user

ClickHouse on Ubuntu 24.04 ubuntu

The first boot service runs before the SSH daemon is ready, so the credentials file is always in place when you log in for the first time.

sudo cat /root/clickhouse-credentials.txt

The file is plain text. It contains the ClickHouse username (default), the generated default user password, and the HTTP and native ports:

# ClickHouse — Per-Instance Credentials
# Generated on first boot: 2026-05-22 23:04:48 UTC
#
# Open the shell with:
# clickhouse-client --password '<password below>'
# CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=<CLICKHOUSE_PASSWORD> CLICKHOUSE_HTTP_PORT=8123 CLICKHOUSE_NATIVE_PORT=9000

Copy these values somewhere secure, such as a password manager or an encrypted vault. Do not commit them to source control.

Each command block in this guide that talks to ClickHouse begins by reading the default user password from the credentials file into a PASSWORD shell variable, so every block is self-contained:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" echo "default user password length: ${#PASSWORD}"

ClickHouse exposes a dedicated health probe on the HTTP port. /ping returns the literal string Ok. and does not require authentication. It is the right endpoint for an external load balancer or a Kubernetes liveness probe:

curl -s http://127.0.0.1:8123/ping

The response is:

Ok.

Run a quick SELECT version() over HTTP with HTTP Basic authentication to confirm that the data plane is healthy too:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" curl -s -u "default:$PASSWORD" "http://127.0.0.1:8123/?query=SELECT+version()"

The response is the installed ClickHouse version:

26.5.1.882

Then confirm that the systemd unit is active:

sudo systemctl is-active clickhouse-server

The response is active.

Component Version Source

ClickHouse server 26.5.1.882 packages.clickhouse.com/deb stable main

clickhouse-client (CLI) bundled with the server packages.clickhouse.com/deb stable main

Default user authentication SHA256 hashed password, rotated on first boot SecureAnyCloud image

Data directory /var/lib/clickhouse (dedicated EBS volume) SecureAnyCloud image

HTTP port 8123 bound on 0.0.0.0

Native TCP port 9000 bound on 0.0.0.0

Inter-server replication port 9009 bound on 0.0.0.0

clickhouse-client is the bundled command-line shell. Open it interactively to run queries against the local node:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$PASSWORD"

Or pass a single query with --query and return to the shell prompt:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$PASSWORD" --query "SELECT version()"

The response is the installed ClickHouse version:

26.5.1.882

ClickHouse ships with a minimal browser SQL playground at /play on the HTTP port. It is the fastest way to explore the server interactively.

Restrict port 8123 to trusted networks before you use it. The playground is open to anyone who can reach the HTTP port and is not suitable for the public internet.

Browse to http://<instance-public-ip>:8123/play and the playground loads.

Type the default user name in the user field, and the per-instance password from /root/clickhouse-credentials.txt in the password field. Type a query into the editor and click Run (or press Ctrl+Enter). A SELECT version(), hostName(), uptime() shows the running ClickHouse build, the host name and uptime in seconds:

system.tables lists every table the server knows about. A query against system.tables, filtered to the system database and ordered by total_rows, shows the size of the server's own internal tables:

The result panel reports the number of rows returned and the query time on the right.

ClickHouse's flagship table engine is MergeTree, the columnar engine that gives ClickHouse its speed. The following block creates a demo database and a small MergeTree table of (timestamp, user_id, action) event rows. It then inserts three rows and runs two analytics queries:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$PASSWORD" --query "CREATE DATABASE IF NOT EXISTS demo" </dev/null clickhouse-client --password "$PASSWORD" --query "CREATE TABLE IF NOT EXISTS demo.events (ts DateTime, user_id UInt32, action String) ENGINE=MergeTree ORDER BY ts" </dev/null clickhouse-client --password "$PASSWORD" --query "INSERT INTO demo.events VALUES (now(), 1, 'login'), (now(), 2, 'click'), (now(), 3, 'purchase')" </dev/null clickhouse-client --password "$PASSWORD" --query "SELECT count() AS total FROM demo.events" </dev/null clickhouse-client --password "$PASSWORD" --query "SELECT action, count() AS n FROM demo.events GROUP BY action ORDER BY action" </dev/null

The total row count is:

3

The GROUP BY query reports one row per distinct action:

click 1 login 1 purchase 1

When you are finished with the demo database, drop it:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$PASSWORD" --query "DROP DATABASE demo" </dev/null clickhouse-client --password "$PASSWORD" --query "SHOW DATABASES" </dev/null

SHOW DATABASES reports the system schemas only:

INFORMATION_SCHEMA default information_schema system

Every ClickHouse driver and integration speaks one of two protocols: the native TCP protocol on port 9000 or the HTTP interface on port 8123. The HTTP interface is the simplest to use from a script or a non-Linux client, because it requires nothing more than an HTTP library. The same SELECT over HTTP looks like:

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" curl -s -u "default:$PASSWORD" "http://127.0.0.1:8123/?query=SELECT+count(*)+FROM+system.tables"

The response is the number of tables the server knows about.

The HTTP interface supports every ClickHouse format (TabSeparated, JSON, CSV, Pretty, etc.). It is the right choice for tools, dashboards and language drivers that prefer HTTP.

The most important security action after launch is to restrict network access. Open ports 8123 and 9000 only to your application tier and your management network.

The /play browser SQL playground and the HTTP SQL endpoint are both served on port 8123. A public port 8123 means anyone can run SQL against your data once they have credentials. The per-instance default user password reduces the risk but does not eliminate it.

To rotate the default user password yourself, generate a new password and apply it inside ClickHouse with ALTER USER:

NEW_PASSWORD="$(openssl rand -hex 16)" OLD_PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$OLD_PASSWORD" --query "ALTER USER default IDENTIFIED WITH sha256_password BY '$NEW_PASSWORD'" </dev/null echo "New password: $NEW_PASSWORD"

Then update /root/clickhouse-credentials.txt so your scripts pick up the new value.

To create a non-administrator user for an application, use SQL-managed access control (access_management is enabled on the default user):

PASSWORD="$(sudo awk -F= '/^CLICKHOUSE_PASSWORD=/ {print $2}' /root/clickhouse-credentials.txt)" clickhouse-client --password "$PASSWORD" --query "CREATE USER analytics IDENTIFIED WITH sha256_password BY '<APP_PASSWORD>'" </dev/null clickhouse-client --password "$PASSWORD" --query "GRANT SELECT ON demo.* TO analytics" </dev/null

Replace <APP_PASSWORD> with the password you generate for the application user.

The image serves the HTTP interface over plain HTTP on port 8123. For a production deployment, put nginx, Caddy or an Application Load Balancer in front of ClickHouse and terminate TLS at the proxy.

The proxy forwards to 127.0.0.1:8123, and the ClickHouse port is closed at the security group. nginx and Caddy can also enforce client certificate authentication for an extra layer of protection on the HTTP endpoint.

ClickHouse's data lives on the dedicated EBS volume mounted at /var/lib/clickhouse. The most cost-effective backup approach is EBS snapshots of that volume, scheduled with Amazon Data Lifecycle Manager or AWS Backup. First check the volume:

df -h /var/lib/clickhouse

The output reports the dedicated data volume and its used and available space:

/dev/nvme1n1 30G 17M 28G 1% /var/lib/clickhouse

For logical backups, use the BACKUP TABLE ... TO Disk('backup', '<path>') and BACKUP DATABASE family of statements. See the ClickHouse backups documentation for the full syntax and supported destinations, including S3.

To upgrade ClickHouse to the latest release on the stable line, run:

sudo apt-get update
sudo apt-get install --only-upgrade -y clickhouse-server clickhouse-client
sudo systemctl restart clickhouse-server

ClickHouse scales horizontally by sharding tables across multiple servers and using replication for fault tolerance. The single-node image is the starting point. For a multi-node deployment:

• Launch additional SecureAnyCloud ClickHouse instances in the same VPC
• Open inter-server replication port 9009 between the nodes in the security group
• Configure remote_servers, <macros> (shard/replica) and ZooKeeper or ClickHouse Keeper in /etc/clickhouse-server/config.d/
• Create ReplicatedMergeTree tables that ClickHouse keeps in sync across the cluster

The ClickHouse architecture documentation covers the canonical sharded and replicated topology in detail.