Example of working with tables via clickhouse-client
- Overview
- Step 1. Create a database
- Step 2. Create a table
- Step 3. Get information about a table
- Step 4. Insert new data into a table
- Step 5. Read data from a table
- Step 6. Update data in a table
- Step 7. Delete data from a table
- Step 8. Alter a table
- Step 9. Detach and attach a table
- Step 10. Delete a table
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
|
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 or1
if the table exists).EXISTS sales;
The
sales
table exists in the currenttest_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 useFORMAT Vertical
at the end of a query:DESCRIBE TABLE sales;\G
DESCRIBE TABLE sales FORMAT Vertical;
Command result in Vertical formatRow 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 thesystem.tables
system table.SELECT * FROM system.tables WHERE name = 'sales';\G
Command resultRow 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 onesales
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 theAPPLY
,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 |
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 │ └────────────┘