Example of working with tables via clickhouse-client

Elena Dvoryadkina

Overview

The easiest way to work with ADQM/ClickHouse tables is to use the clickhouse-client console client, which becomes available on each cluster host after ADQM installation. This client allows you to enter queries, pass them to ClickHouse, and view the results. Client parameters can be passed via the command line or configuration files.

To start working with clickhouse-client, run the command:

$ clickhouse-client -m

This command establishes a connection to the local ClickHouse server (localhost:9000) as the default user without a password and connects to the default database. The terminal switches to the interactive mode of operation with the ClickHouse server and displays the :) prompt, after which you can enter queries. The -m (or --multiline) parameter enables the multiline query input mode — to execute a query, you should end it with a semicolon and press Enter.

$ clickhouse-client -m
ClickHouse client version 24.3.11.7.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.3.11.

:)

To exit clickhouse-client, press Ctrl+D or type exit, quit, q, or logout instead of a query.

The main operations for working with data in ADQM/ClickHouse are listed below (all examples are based on a simple database that stores sales information). For a full list of available commands, refer to the ClickHouse SQL Statements section of the ClickHouse documentation. Before you start executing the commands described in this article, it is also recommended that you read the Concepts → Data Model section of the current ADQM documentation.

Step 1. Create a database

A database to which clickhouse-client connects by default is specified by the Default database parameter on the ADQMDB service configuration page in the ADCM interface (ADQM cluster → Services → ADQMDB → Primary Configuration → Other → Default database). The default value of this parameter is the default database, which is created automatically when ADQM is installed.

To create a new database, use the CREATE DATABASE query.

The following query creates the test_db database (based on the Atomic engine) on all hosts in the default_cluster cluster:

CREATE DATABASE test_db ON CLUSTER default_cluster;

You can change the current database for the session with the USE command, for example:

USE test_db;
NOTE

  • To select a database other than the default one when launching clickhouse-client, use the -d or --database parameter (for example, clickhouse-client -d test_db).

  • If a query requires access to a table that is not in the current database of the session, specify the desired database explicitly using a dot . before a table name.

Step 2. Create a table

To create a new table, use the CREATE TABLE query — specify the table name and table engine, describe all columns (by defining their names, data types, modifiers, data compression codecs), set indexes, constraints, and other settings. You can find an overview of the CREATE TABLE query syntax with descriptions of its parameters and clauses in the section Create a MergeTree table of the article about basic operations with ADQM tables.

The following query creates the sales table with columns of different types to store sales data:

CREATE TABLE sales (
    id UInt32,
    product_name String,
    category String,
    sale_date Date DEFAULT toDate(now()),
    units_sold UInt32,
    price_per_unit Float32,
    region Enum('North America' = 1, 'Europe' = 2, 'Asia' = 3)
)
ENGINE = MergeTree()
ORDER BY id;

Step 3. Get information about a table

To check whether a table exists and get information about it, you can use the following commands:

  • EXISTS <table_name> — checks if a table exists in a database (returns one column with a single value — 0 if the table does not exist or 1 if the table exists).

    EXISTS sales;

    The sales table exists in the current test_db database:

       ┌─result─┐
1. │      1 │
   └────────┘

  • DESCRIBE TABLE <table_name> — returns information about each column of a table (name, data type, default value expression, comment, data compression codec, and TTL expression).

    DESCRIBE TABLE sales;
       ┌─name───────────┬─type─────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
1. │ id             │ UInt32                                               │              │                    │         │                  │                │
2. │ product_name   │ String                                               │              │                    │         │                  │                │
3. │ category       │ String                                               │              │                    │         │                  │                │
4. │ sale_date      │ Date                                                 │ DEFAULT      │ toDate(now())      │         │                  │                │
5. │ units_sold     │ UInt32                                               │              │                    │         │                  │                │
6. │ price_per_unit │ Float32                                              │              │                    │         │                  │                │
7. │ region         │ Enum8('North America' = 1, 'Europe' = 2, 'Asia' = 3) │              │                    │         │                  │                │
   └────────────────┴──────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

    If the command output is too wide to fit in the console window, you can use the Vertical format for the output data — in this case, each value is printed on a separate line. To enable this, specify \G instead of or after the semicolon or use FORMAT Vertical at the end of a query:

    DESCRIBE TABLE sales;\G
    DESCRIBE TABLE sales FORMAT Vertical;
    Command result in Vertical format
    Row 1:
──────
name:               id
type:               UInt32
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

Row 2:
──────
name:               product_name
type:               String
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

Row 3:
──────
name:               category
type:               String
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

Row 4:
──────
name:               sale_date
type:               Date
default_type:       DEFAULT
default_expression: toDate(now())
comment:
codec_expression:
ttl_expression:

Row 5:
──────
name:               units_sold
type:               UInt32
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

Row 6:
──────
name:               price_per_unit
type:               Float32
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

Row 7:
──────
name:               region
type:               Enum8('North America' = 1, 'Europe' = 2, 'Asia' = 3)
default_type:
default_expression:
comment:
codec_expression:
ttl_expression:

    To get a full list of formats ADQM/ClickHouse supports for input and output data, see the Formats for Input and Output Data article of ClickHouse documentation.

  • SELECT * FROM system.tables WHERE name = '<table_name>' — returns metadata for a table with the specified name from the system.tables system table.

    SELECT * FROM system.tables WHERE name = 'sales';\G
    Command result
    Row 1:
──────
database:                      test_db
name:                          sales
uuid:                          4a8690b9-e048-4b55-acea-27586cd2380f
engine:                        MergeTree
is_temporary:                  0
data_paths:                    ['/var/lib/clickhouse/store/4a8/4a8690b9-e048-4b55-acea-27586cd2380f/']
metadata_path:                 /var/lib/clickhouse/store/a8e/a8e6a2c0-ea2c-4c0b-8669-de931f53178d/sales.sql
metadata_modification_time:    2025-01-16 15:17:05
metadata_version:              0
dependencies_database:         []
dependencies_table:            []
create_table_query:            CREATE TABLE test_db.sales (`id` UInt32, `product_name` String, `category` String, `sale_date` Date DEFAULT toDate(now()), `units_sold` UInt32, `price_per_unit` Float32, `region` Enum8('North America' = 1, 'Europe' = 2, 'Asia' = 3)) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192
engine_full:                   MergeTree ORDER BY id SETTINGS index_granularity = 8192
as_select:
partition_key:
sorting_key:                   id
primary_key:                   id
sampling_key:
storage_policy:                default
total_rows:                    0
total_bytes:                   0
total_bytes_uncompressed:      0
parts:                         0
active_parts:                  0
total_marks:                   0
lifetime_rows:                 ᴺᵁᴸᴸ
lifetime_bytes:                ᴺᵁᴸᴸ
comment:
has_own_data:                  1
loading_dependencies_database: []
loading_dependencies_table:    []
loading_dependent_database:    []
loading_dependent_table:       []

  • SHOW CREATE TABLE <table_name> — returns a single column containing the text of the CREATE TABLE query used to create the specified table.

    SHOW CREATE TABLE sales;\G
    Row 1:
──────
statement: CREATE TABLE test_db.sales
(
    `id` UInt32,
    `product_name` String,
    `category` String,
    `sale_date` Date DEFAULT toDate(now()),
    `units_sold` UInt32,
    `price_per_unit` Float32,
    `region` Enum8('North America' = 1, 'Europe' = 2, 'Asia' = 3)
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192

  • SHOW TABLES — lists all tables that exist in a database.

    SHOW TABLES;

    The current test_db database contains only one sales table:

       ┌─name──┐
1. │ sales │
   └───────┘

Step 4. Insert new data into a table

To add new data to a table, use the INSERT INTO query:

INSERT INTO <table_name> [(<c1>, <c2>, <c3>)] VALUES (v11, v12, v13), (v21, v22, v23), ...;

After a table name, you can optionally specify columns to insert data into — list the column names separated by commas or use an expression with * and modifiers such as APPLY, EXCEPT, REPLACE. After the VALUES keyword, list the column values ​​separated by commas in the same order as the column names in the (<c1>, <c2>, <c3>) list. If columns to accept data are not defined explicitly, you should specify values ​​of the corresponding types for all columns of the table — the given values ​​will appear in the columns according to the order in which they were declared during the table creation.

If the specified list of columns does not include all existing columns of the table, the rest columns are filled with:

  • values ​​calculated from the DEFAULT expressions specified for columns in the table definition;

  • zeros and empty strings, if the DEFAULT expressions for columns are not defined.

The following five separate queries each add one row to the sales table:

INSERT INTO sales VALUES (1, 'Laptop', 'Electronics', '2025-01-03', 5, 999.99, 1);
INSERT INTO sales VALUES (2, 'Smartphone', 'Electronics', '2025-01-04', 10, 699.99, 2);
INSERT INTO sales VALUES (3, 'Headphones', 'Electronics', '2025-01-04', 25, 49.99, 3);
INSERT INTO sales VALUES (4, 'Desk Chair', 'Furniture', '2025-01-05', 3, 149.99, 1);
INSERT INTO sales VALUES (5, 'Coffee Table', 'Furniture', '2025-01-05', 2, 249.99, 2);

To insert multiple rows via one INSERT INTO query, list all data tuples separated by commas after the VALUES keyword:

INSERT INTO sales VALUES
(6, 'Notebook', 'Stationery', '2025-01-06', 100, 1.99, 3),
(7, 'Pen', 'Stationery', '2025-01-06', 200, 0.49, 1),
(8, 'Water Bottle', 'Accessories', '2025-01-07', 15, 9.99, 2),
(9, 'Backpack', 'Accessories', '2025-01-07', 8, 59.99, 3),
(10, 'Monitor', 'Electronics', '2025-01-08', 6, 199.99, 1),
(11, 'Keyboard', 'Electronics', '2025-01-08', 12, 49.99, 2);

The following query adds data to all columns except sale_date, so as many values ​​are passed as there are defined columns, and the sale_date column will be filled with the default value ​​(the current date):

INSERT INTO sales (* EXCEPT(sale_date)) VALUES (12, 'Office Chair', 'Furniture',  4, 199.99, 3);

You can also use the DEFAULT keyword to insert default values:

INSERT INTO sales VALUES (13, 'Printer', 'Electronics', DEFAULT, 2, 299.99, 1);

You can also pass data in any format supported by ClickHouse to the INSERT INTO query (see the Input column in the Formats for Input and Output Data table). To do this, specify a format in a query explicitly using the FORMAT keyword, for example:

INSERT INTO sales FORMAT JSONEachRow
{"id":14,"product_name":"Cap","category":"Accessories","sale_date":"2025-01-09","units_sold":30,"price_per_unit":5.99,"region":2}
{"id":15,"product_name":"Binder","category":"Stationery","sale_date":"2025-01-09","units_sold":50,"price_per_unit":2.99,"region":3};
NOTE

Data can also be imported into an ADQM/ClickHouse table from files of various formats — see Import from files to get examples.

Step 5. Read data from a table

To read data from a table, use the SELECT query. Here are some specifics of how to construct a query:

  • After the SELECT statement, specify a list of columns to retrieve data from (list names of individual columns or use the * symbol to get data from all columns) and/or expressions to be evaluated over a set of rows (for example, aggregate or window functions). When creating a list of columns/expressions, you can use the APPLY, EXCEPT, REPLACE modifiers and their combinations.

  • After the FROM keyword, specify the name of a table from which to retrieve data. To read data from two tables and combine them into a single result set, use the JOIN operator.

    A subquery or table function can also be used as a source for selecting data.

  • In the WHERE clause, describe conditions for filtering rows to read.

  • For additional processing of the output results (sorting, grouping), you can use ORDER BY, GROUP BY, HAVING, and other clauses.

Examples of various SELECT queries are listed below.

Select all data

The following query selects all data from the sales table:

SELECT * FROM sales;

Since this example uses the * symbol instead of column names and does not contain the WHERE condition, all columns and rows of the table are included in the results:

    ┌─id─┬─product_name─┬─category────┬──sale_date─┬─units_sold─┬─price_per_unit─┬─region────────┐
 1. │  1 │ Laptop       │ Electronics │ 2025-01-03 │          5 │         999.99 │ North America │
 2. │  2 │ Smartphone   │ Electronics │ 2025-01-04 │         10 │         699.99 │ Europe        │
 3. │  3 │ Headphones   │ Electronics │ 2025-01-04 │         25 │          49.99 │ Asia          │
 4. │  4 │ Desk Chair   │ Furniture   │ 2025-01-05 │          3 │         149.99 │ North America │
 5. │  5 │ Coffee Table │ Furniture   │ 2025-01-05 │          2 │         249.99 │ Europe        │
 6. │  6 │ Notebook     │ Stationery  │ 2025-01-06 │        100 │           1.99 │ Asia          │
 7. │  7 │ Pen          │ Stationery  │ 2025-01-06 │        200 │           0.49 │ North America │
 8. │  8 │ Water Bottle │ Accessories │ 2025-01-07 │         15 │           9.99 │ Europe        │
 9. │  9 │ Backpack     │ Accessories │ 2025-01-07 │          8 │          59.99 │ Asia          │
10. │ 10 │ Monitor      │ Electronics │ 2025-01-08 │          6 │         199.99 │ North America │
11. │ 11 │ Keyboard     │ Electronics │ 2025-01-08 │         12 │          49.99 │ Europe        │
12. │ 12 │ Office Chair │ Furniture   │ 2025-01-16 │          4 │         199.99 │ Asia          │
13. │ 13 │ Printer      │ Electronics │ 2025-01-16 │          2 │         299.99 │ North America │
14. │ 14 │ Cap          │ Accessories │ 2025-01-09 │         30 │           5.99 │ Europe        │
15. │ 15 │ Binder       │ Stationery  │ 2025-01-09 │         50 │           2.99 │ Asia          │
    └────┴──────────────┴─────────────┴────────────┴────────────┴────────────────┴───────────────┘

Filter data

The next query returns sales data only for January 7, 2025:

SELECT
    product_name,
    sale_date,
    units_sold
FROM sales
WHERE sale_date = '2025-01-07';
   ┌─product_name─┬──sale_date─┬─units_sold─┐
1. │ Water Bottle │ 2025-01-07 │         15 │
2. │ Backpack     │ 2025-01-07 │          8 │
   └──────────────┴────────────┴────────────┘

Sort the results

The following query returns the top 10 sales from the sales table in descending order:

SELECT
    sale_date,
    product_name,
    units_sold,
    price_per_unit,
    round(units_sold * price_per_unit, 2) AS revenue
FROM sales
ORDER BY revenue DESC
LIMIT 10;
    ┌──sale_date─┬─product_name─┬─units_sold─┬─price_per_unit─┬─revenue─┐
 1. │ 2025-01-04 │ Smartphone   │         10 │         699.99 │  6999.9 │
 2. │ 2025-01-03 │ Laptop       │          5 │         999.99 │ 4999.95 │
 3. │ 2025-01-04 │ Headphones   │         25 │          49.99 │ 1249.75 │
 4. │ 2025-01-08 │ Monitor      │          6 │         199.99 │ 1199.94 │
 5. │ 2025-01-16 │ Office Chair │          4 │         199.99 │  799.96 │
 6. │ 2025-01-16 │ Printer      │          2 │         299.99 │  599.98 │
 7. │ 2025-01-08 │ Keyboard     │         12 │          49.99 │  599.88 │
 8. │ 2025-01-05 │ Coffee Table │          2 │         249.99 │  499.98 │
 9. │ 2025-01-07 │ Backpack     │          8 │          59.99 │  479.92 │
10. │ 2025-01-05 │ Desk Chair   │          3 │         149.99 │  449.97 │
    └────────────┴──────────────┴────────────┴────────────────┴─────────┘

Group the results

The next query groups the sales stored in the sales table by product category and finds a product with the highest unit price in each category:

SELECT
    category,
    argMax(product_name, price_per_unit) AS most_expensive_product,
    max(price_per_unit) AS highest_price
FROM sales
GROUP BY category
ORDER BY highest_price DESC;
   ┌─category────┬─most_expensive_product─┬─highest_price─┐
1. │ Electronics │ Laptop                 │        999.99 │
2. │ Furniture   │ Coffee Table           │        249.99 │
3. │ Accessories │ Backpack               │         59.99 │
4. │ Stationery  │ Binder                 │          2.99 │
   └─────────────┴────────────────────────┴───────────────┘

The following query returns the daily revenue for each region:

SELECT
    sale_date,
    region,
    round(SUM(units_sold * price_per_unit), 2) AS daily_revenue
FROM sales
GROUP BY sale_date, region
ORDER BY sale_date, region;
    ┌──sale_date─┬─region────────┬─daily_revenue─┐
 1. │ 2025-01-03 │ North America │       4999.95 │
 2. │ 2025-01-04 │ Europe        │        6999.9 │
 3. │ 2025-01-04 │ Asia          │       1249.75 │
 4. │ 2025-01-05 │ North America │        449.97 │
 5. │ 2025-01-05 │ Europe        │        499.98 │
 6. │ 2025-01-06 │ North America │            98 │
 7. │ 2025-01-06 │ Asia          │           199 │
 8. │ 2025-01-07 │ Europe        │        149.85 │
 9. │ 2025-01-07 │ Asia          │        479.92 │
10. │ 2025-01-08 │ North America │       1199.94 │
11. │ 2025-01-08 │ Europe        │        599.88 │
12. │ 2025-01-09 │ Europe        │         179.7 │
13. │ 2025-01-09 │ Asia          │         149.5 │
14. │ 2025-01-14 │ North America │        599.98 │
15. │ 2025-01-14 │ Asia          │        799.96 │
    └────────────┴───────────────┴───────────────┘

Combine SELECT with CREATE and INSERT INTO

You can use the SELECT statement to copy data from one table to another via the CREATE and INSERT INTO commands.

The query below creates a new sales_copy table with the same structure as the sales table and also copies the rows corresponding to sales up to and including January 7, 2025 from the original table into the new one using the SELECT command:

CREATE TABLE sales_copy
ENGINE = MergeTree
ORDER BY id
AS SELECT * FROM sales
WHERE sale_date <= '2025-01-07';

The following query adds the rest rows (sales after January 7, 2025) from the source sales table to sales_copy using the INSERT INTO command:

INSERT INTO sales_copy SELECT * FROM sales WHERE sale_date > '2025-01-07';

To verify that the contents of these two tables are identical, select data from both tables:

SELECT * FROM sales;
    ┌─id─┬─product_name─┬─category────┬──sale_date─┬─units_sold─┬─price_per_unit─┬─region────────┐
 1. │  1 │ Laptop       │ Electronics │ 2025-01-03 │          5 │         999.99 │ North America │
 2. │  2 │ Smartphone   │ Electronics │ 2025-01-04 │         10 │         699.99 │ Europe        │
 3. │  3 │ Headphones   │ Electronics │ 2025-01-04 │         25 │          49.99 │ Asia          │
 4. │  4 │ Desk Chair   │ Furniture   │ 2025-01-05 │          3 │         149.99 │ North America │
 5. │  5 │ Coffee Table │ Furniture   │ 2025-01-05 │          2 │         249.99 │ Europe        │
 6. │  6 │ Notebook     │ Stationery  │ 2025-01-06 │        100 │           1.99 │ Asia          │
 7. │  7 │ Pen          │ Stationery  │ 2025-01-06 │        200 │           0.49 │ North America │
 8. │  8 │ Water Bottle │ Accessories │ 2025-01-07 │         15 │           9.99 │ Europe        │
 9. │  9 │ Backpack     │ Accessories │ 2025-01-07 │          8 │          59.99 │ Asia          │
10. │ 10 │ Monitor      │ Electronics │ 2025-01-08 │          6 │         199.99 │ North America │
11. │ 11 │ Keyboard     │ Electronics │ 2025-01-08 │         12 │          49.99 │ Europe        │
12. │ 12 │ Office Chair │ Furniture   │ 2025-01-16 │          4 │         199.99 │ Asia          │
13. │ 13 │ Printer      │ Electronics │ 2025-01-16 │          2 │         299.99 │ North America │
14. │ 14 │ Cap          │ Accessories │ 2025-01-09 │         30 │           5.99 │ Europe        │
15. │ 15 │ Binder       │ Stationery  │ 2025-01-09 │         50 │           2.99 │ Asia          │
    └────┴──────────────┴─────────────┴────────────┴────────────┴────────────────┴───────────────┘
SELECT * FROM sales_copy;
    ┌─id─┬─product_name─┬─category────┬──sale_date─┬─units_sold─┬─price_per_unit─┬─region────────┐
 1. │  1 │ Laptop       │ Electronics │ 2025-01-03 │          5 │         999.99 │ North America │
 2. │  2 │ Smartphone   │ Electronics │ 2025-01-04 │         10 │         699.99 │ Europe        │
 3. │  3 │ Headphones   │ Electronics │ 2025-01-04 │         25 │          49.99 │ Asia          │
 4. │  4 │ Desk Chair   │ Furniture   │ 2025-01-05 │          3 │         149.99 │ North America │
 5. │  5 │ Coffee Table │ Furniture   │ 2025-01-05 │          2 │         249.99 │ Europe        │
 6. │  6 │ Notebook     │ Stationery  │ 2025-01-06 │        100 │           1.99 │ Asia          │
 7. │  7 │ Pen          │ Stationery  │ 2025-01-06 │        200 │           0.49 │ North America │
 8. │  8 │ Water Bottle │ Accessories │ 2025-01-07 │         15 │           9.99 │ Europe        │
 9. │  9 │ Backpack     │ Accessories │ 2025-01-07 │          8 │          59.99 │ Asia          │
10. │ 10 │ Monitor      │ Electronics │ 2025-01-08 │          6 │         199.99 │ North America │
11. │ 11 │ Keyboard     │ Electronics │ 2025-01-08 │         12 │          49.99 │ Europe        │
12. │ 12 │ Office Chair │ Furniture   │ 2025-01-16 │          4 │         199.99 │ Asia          │
13. │ 13 │ Printer      │ Electronics │ 2025-01-16 │          2 │         299.99 │ North America │
14. │ 14 │ Cap          │ Accessories │ 2025-01-09 │         30 │           5.99 │ Europe        │
15. │ 15 │ Binder       │ Stationery  │ 2025-01-09 │         50 │           2.99 │ Asia          │
    └────┴──────────────┴─────────────┴────────────┴────────────┴────────────────┴───────────────┘

Step 6. Update data in a table

To update data in a table, use the ALTER TABLE…​UPDATE query, specifying a list of new values for columns ​​(or expressions to calculate values) after the UPDATE keyword and a condition for selecting rows in which you want to replace the values after WHERE:

ALTER TABLE <table_name> UPDATE <column_name> = <new_value>[, ...] WHERE <filter_expr>;

For example, the following query increases the price of each product in the Accessories category in the sales_copy table by 2 percent:

ALTER TABLE sales_copy
UPDATE price_per_unit = round(price_per_unit*1.02, 2)
WHERE category = 'Accessories';

To check the query results, you can output product data (name and price) from the Accessories category using the SELECT command:

SELECT product_name, price_per_unit FROM sales_copy WHERE category='Accessories';
   ┌─product_name─┬─price_per_unit─┐
1. │ Water Bottle │          10.19 │
2. │ Backpack     │          61.19 │
3. │ Cap          │           6.11 │
   └──────────────┴────────────────┘

Step 7. Delete data from a table

To delete data rows from a table according to the specified condition, you can use the ALTER TABLE…​DELETE (for any tables) or DELETE FROM (for MergeTree tables) query — see Delete data for more details.

The following query deletes rows corresponding to sales up to and including January 5, 2025 from the sales_copy table:

ALTER TABLE sales_copy DELETE WHERE sale_date <= '2025-01-05';

To check the query results, select the table data and make sure that it contains only sales after January 5, 2025:

    ┌─id─┬─product_name─┬─category────┬──sale_date─┬─units_sold─┬─price_per_unit─┬─region────────┐
 1. │  6 │ Notebook     │ Stationery  │ 2025-01-06 │        100 │           1.99 │ Asia          │
 2. │  7 │ Pen          │ Stationery  │ 2025-01-06 │        200 │           0.49 │ North America │
 3. │  8 │ Water Bottle │ Accessories │ 2025-01-07 │         15 │          10.19 │ Europe        │
 4. │  9 │ Backpack     │ Accessories │ 2025-01-07 │          8 │          61.19 │ Asia          │
 5. │ 10 │ Monitor      │ Electronics │ 2025-01-08 │          6 │         199.99 │ North America │
 6. │ 11 │ Keyboard     │ Electronics │ 2025-01-08 │         12 │          49.99 │ Europe        │
 7. │ 12 │ Office Chair │ Furniture   │ 2025-01-16 │          4 │         199.99 │ Asia          │
 8. │ 13 │ Printer      │ Electronics │ 2025-01-16 │          2 │         299.99 │ North America │
 9. │ 14 │ Cap          │ Accessories │ 2025-01-09 │         30 │           6.11 │ Europe        │
10. │ 15 │ Binder       │ Stationery  │ 2025-01-09 │         50 │           2.99 │ Asia          │
    └────┴──────────────┴─────────────┴────────────┴────────────┴────────────────┴───────────────┘

The following query deletes a row related to a sale of the Keyboard product in the Europe region from the sales_copy table:

DELETE FROM sales_copy WHERE product_name = 'Keyboard' AND region = 'Europe';

The specified row was successfully deleted:

   ┌─id─┬─product_name─┬─category────┬──sale_date─┬─units_sold─┬─price_per_unit─┬─region────────┐
1. │  6 │ Notebook     │ Stationery  │ 2025-01-06 │        100 │           1.99 │ Asia          │
2. │  7 │ Pen          │ Stationery  │ 2025-01-06 │        200 │           0.49 │ North America │
3. │  8 │ Water Bottle │ Accessories │ 2025-01-07 │         15 │          10.19 │ Europe        │
4. │  9 │ Backpack     │ Accessories │ 2025-01-07 │          8 │          61.19 │ Asia          │
5. │ 10 │ Monitor      │ Electronics │ 2025-01-08 │          6 │         199.99 │ North America │
6. │ 12 │ Office Chair │ Furniture   │ 2025-01-16 │          4 │         199.99 │ Asia          │
7. │ 13 │ Printer      │ Electronics │ 2025-01-16 │          2 │         299.99 │ North America │
8. │ 14 │ Cap          │ Accessories │ 2025-01-09 │         30 │           6.11 │ Europe        │
9. │ 15 │ Binder       │ Stationery  │ 2025-01-09 │         50 │           2.99 │ Asia          │
   └────┴──────────────┴─────────────┴────────────┴────────────┴────────────────┴───────────────┘

Step 8. Alter a table

To change the structure (columns) of a table, use the ALTER TABLE query (supported by the MergeTree, Merge, and Distributed table engines):

ALTER TABLE <table_name> ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN <column_name> ...

In one query, you can specify multiple comma-separated actions to perform on table columns.

The following query renames the product_name column to product and adds a new store_id column:

ALTER TABLE sales_copy RENAME COLUMN product_name TO product, ADD COLUMN store_id UInt8;

To check that the column is renamed and the new store_id column is added, run the DESCRIBE TABLE query:

DESCRIBE TABLE sales_copy;
   ┌─name───────────┬─type─────────────────────────────────────────────────┬
1. │ id             │ UInt32                                               │
2. │ product        │ String                                               │
3. │ category       │ String                                               │
4. │ sale_date      │ Date                                                 │
5. │ units_sold     │ UInt32                                               │
6. │ price_per_unit │ Float32                                              │
7. │ region         │ Enum8('North America' = 1, 'Europe' = 2, 'Asia' = 3) │
8. │ store_id       │ UInt8                                                │
   └────────────────┴──────────────────────────────────────────────────────┴
NOTE

The ALTER TABLE query also allows you to perform various manipulations with partitions, data parts, constraints, indexes, table projections, add comments to tables, set TTL rules — see the detailed descriptions in the corresponding articles in the ALTER section of the ClickHouse documentation.

Step 9. Detach and attach a table

With the DETACH TABLE query, you can detach a table, which means it will be "invisible" to the server (but the table data and metadata will not be deleted).

The following query detaches the sales_copy table:

DETACH TABLE sales_copy;

Make sure the sales_copy table is not in the list of database tables:

SHOW tables;
   ┌─name──┐
1. │ sales │
   └───────┘

The next time the server starts, it will read the table metadata and "see" it again. To prevent the server from re-enabling the table automatically on restart, detach the table permanently — to do this, use the PERMANENTLY keyword at the end of the DETACH TABLE query.

To attach a table back (regardless of whether it was permanently detached or not), run the ATTACH TABLE query:

ATTACH TABLE sales_copy;

Check that the ClickHouse server can "see" the sales_copy table again:

SHOW tables;
   ┌─name───────┐
1. │ sales      │
2. │ sales_copy │
   └────────────┘

Step 10. Delete a table

To remove a table permanently, use the DROP TABLE query:

DROP TABLE sales_copy;

The table has been successfully removed from the database:

SHOW tables;
   ┌─name──┐
1. │ sales │
   └───────┘
Found a mistake? Seleсt text and press Ctrl+Enter to report it