ClickHouse SQL Fundamentals: CREATE, TABLE, Views, ALTER, Partitioning, Import/Export, and Mutation Operations

The article introduces ClickHouse as a column‑oriented database and presents the basic SQL syntax needed to work with it, starting with CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE = engine]; and the USE db_name; command for switching databases.

It then details table creation using CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine and shows three methods: direct creation, creating a table as a copy of another, and CTAS (Create Table As Select). Example syntax includes:

create table table_name (id UInt16, name String) engine=TinyLog;

Column definitions can use DEFAULT, MATERIALIZED, or ALIAS expressions, with explanations of how each behaves during insert, query, and storage phases. The article also demonstrates how to modify column defaults via

ALTER TABLE db_name.table MODIFY COLUMN col_name DEFAULT value;

.

Temporary tables are created by adding the TEMPORARY keyword, and their lifecycle is session‑bound, using only the Memory engine. The article lists their special characteristics.

Partitioned tables are explained, distinguishing partitioning from sharding. An example creates a MergeTree table partitioned by month:

create table table_test_partition (id String, url String, eventTime DateTime) engine = MergeTree() partition by toYYYYMM(eventTime) order by id;

Queries to the system.parts table reveal partition metadata, and the article shows how to query, drop, replace, detach, and attach partitions.

View creation is covered for both ordinary and materialized views. Ordinary views are simple query proxies ( CREATE VIEW view_name AS SELECT ...;), while materialized views store data using a specified engine and can be populated immediately with the POPULATE modifier.

The DDL section outlines how to execute statements across a ClickHouse cluster using ON CLUSTER cluster_name, enabling distributed creation of databases and tables.

Data import and export are demonstrated with INSERT INTO statements, JSONEachRow format, and command‑line examples for exporting to CSV and importing from CSV using clickhouse-client.

Finally, the article explains ClickHouse mutation queries for bulk updates and deletes, noting their asynchronous nature and lack of transaction support. Deletion syntax is shown as: ALTER TABLE db_name.table_name DELETE WHERE filter_expr; It also describes how mutation logs are generated and how old data is eventually cleaned up during MergeTree merges.