Evaluating engineering tools? Get the comparison in Google Sheets

Connection

clickhouse-client --host=hostname --port=9000 --user=username --password=password
Connect to a ClickHouse server using the CLI client

clickhouse-client --host=hostname --user=username --password=password --database=dbname
Connect with a specific database

clickhouse-client --host=hostname --secure
Connect using SSL/TLS

Basic Queries

SELECT * FROM table_name LIMIT 10
View first 10 rows from a table

DESCRIBE TABLE table_name
Show table structure

SHOW TABLES
List all tables in the current database

SHOW DATABASES
List all databases

USE database_name
Switch to a specific database

Data Exploration

SELECT count() FROM table_name
Count rows in a table

SELECT * FROM system.metrics
View system metrics

SELECT * FROM system.tables
Get information about all tables

SELECT * FROM system.processes
View current queries being executed

SELECT * FROM system.query_log
View query history (if enabled)

Data Manipulation

INSERT INTO table_name VALUES (val1, val2, ...)
Insert a single row

INSERT INTO table_name SELECT * FROM another_table
Insert data from query results

INSERT INTO table_name FORMAT CSV 'data.csv'
Import data from a CSV file

ALTER TABLE table_name ADD COLUMN column_name Type
Add a new column

ALTER TABLE table_name DROP COLUMN column_name
Remove a column

Schema Management

CREATE DATABASE db_name
Create a new database

CREATE TABLE table_name (id UInt32, name String) ENGINE = MergeTree() ORDER BY id
Create a basic MergeTree table

DROP TABLE table_name
Delete a table

DROP DATABASE db_name
Delete a database

TRUNCATE TABLE table_name
Remove all rows from a table

Performance Analysis

EXPLAIN SELECT * FROM table_name
Show query execution plan

OPTIMIZE TABLE table_name
Force a merge of table parts

SELECT * FROM system.parts WHERE table = 'table_name'
Examine table parts

SELECT * FROM system.merges
View active merges

Administration

SYSTEM RELOAD CONFIG
Reload configuration without restarting

SYSTEM STOP MERGES table_name
Stop background merges for a table

SYSTEM START MERGES table_name
Resume background merges

KILL QUERY WHERE query_id='id'
Terminate a specific query