ClickHouse is a fast, open-source columnar database management system designed for real-time analytics on massive volumes of data. Whether you're analyzing user behavior, processing event logs, or building dashboards, ClickHouse delivers sub-second query performance with remarkable efficiency.
This quick-start guide walks you through the core steps of working with ClickHouse like creating a database, defining tables, inserting data, querying table, and cleaning up when you're done. By the end, you'll be able to run your own ClickHouse instance confidently and understand how to structure data for high-performance analytical queries.
1. What is a Database in ClickHouse?
In ClickHouse, a database is a logical container that holds tables. Think of it as a namespace, it helps to organize tables into groups, much like folders organize files. Using separate databases for different usecases, makes it easier to manage and query data in large or multi-project environments.
1.1 How to Create a Database in ClickHouse
To create a database, you use the CREATE DATABASE SQL command.
print('hello world!')
This command creates a new logical namespace for storing tables.
1.2 Naming Conventions for Databases
Follow these best practices for naming databases:
· Use lowercase letters (ClickHouse is case-sensitive)
· Use underscores _ instead of spaces
· Be descriptive and concise
· Avoid reserved keywords
· Include project or domain context
1.3 Examples of good database names:
· ecommerce_analytics
· user_behavior_logs
· sales_reporting
· iot_sensor_data
1.4 Follow below step by step procedure to create a database
Step 1: Open your terminal, Run the ClickHouse client by execuitng below command.
clickhouse-client
$clickhouse-client ClickHouse client version 25.5.1.1919 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 25.5.1. Warnings: * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries. krishna :)
Create ecommerce_analytics database by executing below statement.
CREATE DATABASE ecommerce_analytics;
krishna :) CREATE DATABASE ecommerce_analytics; CREATE DATABASE ecommerce_analytics Query id: 1ff25a02-cae4-4729-b02f-82599a3bfa13 Ok. 0 rows in set. Elapsed: 0.005 sec.
Verify the database was created or not by executing following statement.
SHOW DATABASES;
krishna :) SHOW DATABASES; SHOW DATABASES Query id: aa34d654-a3c4-4371-9137-9dedcdf9deac ┌─name────────────────┐ 1. │ INFORMATION_SCHEMA │ 2. │ default │ 3. │ ecommerce_analytics │ 4. │ information_schema │ 5. │ system │ └─────────────────────┘ 5 rows in set. Elapsed: 0.003 sec.
2. Tables in Clickhouse
In ClickHouse, a table is a way to store structured data, similar to tables in other databases. Each table has columns with defined data types and holds rows of data. But unlike regular databases that store data by rows, ClickHouse stores data by columns, which makes it much faster for analyzing large amounts of data.
2.1 How to Create a Table in ClickHouse?
Basic Syntax
CREATE TABLE my_database.my_table ( column1 DataType, column2 DataType ) ENGINE = EngineName ORDER BY column;
Here,
· ENGINE specifies how the data is stored and queried.
· ORDER BY defines the primary sort key (very important for performance in ClickHouse).
2.2 Naming Conventions for Tables
Follow these guidelines:
· Use lowercase letters.
· Use underscores _ for multi-word names.
· Be descriptive but concise.
· Use business or domain context.
Examples of meaningful table names in an ecommerce_analytics database:
· customer_orders
· product_views
· daily_sales_summary
· user_clickstream
· cart_abandonment_events
2.3 Create customer_orders table.
Let’s create a customer_orders table in ecommerce_analytics database by executing following statement.
CREATE TABLE ecommerce_analytics.customer_orders ( order_id UInt64, customer_id UInt32, order_date Date, order_amount Float64, payment_method String ) ENGINE = MergeTree ORDER BY order_date;
krishna :) CREATE TABLE ecommerce_analytics.customer_orders ( order_id UInt64, customer_id UInt32, order_date Date, order_amount Float64, payment_method String ) ENGINE = MergeTree ORDER BY order_date; CREATE TABLE ecommerce_analytics.customer_orders ( `order_id` UInt64, `customer_id` UInt32, `order_date` Date, `order_amount` Float64, `payment_method` String ) ENGINE = MergeTree ORDER BY order_date Query id: 6b24dc58-3ecf-4b61-9e2f-8d2881569987 Ok. 0 rows in set. Elapsed: 0.020 sec.
To list all tables in a specific database in ClickHouse, you can use the following SQL command:
SHOW TABLES FROM database_name;
For example, to list all tables in the ecommerce_analytics database:
SHOW TABLES FROM ecommerce_analytics;
krishna :) SHOW TABLES FROM ecommerce_analytics; SHOW TABLES FROM ecommerce_analytics Query id: dcfa3c75-f4ed-4a0b-9373-a425b17c2f38 ┌─name────────────┐ 1. │ customer_orders │ └─────────────────┘ 1 row in set. Elapsed: 0.003 sec.
If you're already using the database (via USE), you can simply run:
SHOW TABLES;
krishna :) USE ecommerce_analytics; USE ecommerce_analytics Query id: f3a93674-3fac-48a3-8e37-5637ffac8792 Ok. 0 rows in set. Elapsed: 0.003 sec. krishna :) SHOW TABLES; SHOW TABLES Query id: 3416f9ae-ddef-4bdc-a171-8bc76200e616 ┌─name────────────┐ 1. │ customer_orders │ └─────────────────┘ 1 row in set. Elapsed: 0.003 sec.
3. Insert Data into table
In ClickHouse, you use the standard SQL INSERT INTO statement to insert data into a table. The basic syntax is:
INSERT INTO database_name.table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...;
Example: Insert Data into customer_orders
INSERT INTO ecommerce_analytics.customer_orders (order_id, customer_id, order_date, order_amount, payment_method) VALUES (1001, 201, '2025-05-01', 259.99, 'credit_card'), (1002, 202, '2025-05-02', 89.50, 'paypal'), (1003, 203, '2025-05-02', 145.00, 'net_banking');
krishna :) INSERT INTO ecommerce_analytics.customer_orders (order_id, customer_id, order_date, order_amount, payment_method) VALUES (1001, 201, '2025-05-01', 259.99, 'credit_card'), (1002, 202, '2025-05-02', 89.50, 'paypal'), (1003, 203, '2025-05-02', 145.00, 'net_banking'); INSERT INTO ecommerce_analytics.customer_orders (order_id, customer_id, order_date, order_amount, payment_method) FORMAT Values Query id: 2ab101a9-08ed-4485-bb5a-c7d4d8c717f7 Ok. 3 rows in set. Elapsed: 0.014 sec.
Keep a note of following points while inserting the data into a Clickhous table
· Date format should be 'YYYY-MM-DD'.
· No quotes around numbers.
· ClickHouse expects all values to be castable to the correct type.
· Insert multiple rows in one statement for better performance.
4. Query data from a table
Let’s now explore how to query (select) data from a table in ClickHouse using SQL SELECT statements.
Basic Syntax
SELECT column1, column2, ... FROM database_name.table_name [WHERE condition] [ORDER BY column] [LIMIT n];
Example 1: Select All Rows and columns
SELECT * FROM ecommerce_analytics.customer_orders;
Above statement teturns all columns and rows in the table.
krishna :) SELECT * FROM ecommerce_analytics.customer_orders; SELECT * FROM ecommerce_analytics.customer_orders Query id: 2798394c-72fa-4722-9ef7-b25265e88c87 ┌─order_id─┬─customer_id─┬─order_date─┬─order_amount─┬─payment_method─┐ 1. │ 1001 │ 201 │ 2025-05-01 │ 259.99 │ credit_card │ 2. │ 1002 │ 202 │ 2025-05-02 │ 89.5 │ paypal │ 3. │ 1003 │ 203 │ 2025-05-02 │ 145 │ net_banking │ └──────────┴─────────────┴────────────┴──────────────┴────────────────┘ 3 rows in set. Elapsed: 0.009 sec.
Example 2: Select Specific Columns
SELECT order_id, order_amount FROM ecommerce_analytics.customer_orders;
Shows only the order_id and order_amount for each order.
krishna :) SELECT order_id, order_amount FROM ecommerce_analytics.customer_orders; SELECT order_id, order_amount FROM ecommerce_analytics.customer_orders Query id: 9f598a7c-914b-41aa-8c40-6181a3cb4bd2 ┌─order_id─┬─order_amount─┐ 1. │ 1001 │ 259.99 │ 2. │ 1002 │ 89.5 │ 3. │ 1003 │ 145 │ └──────────┴──────────────┘ 3 rows in set. Elapsed: 0.003 sec.
Example 3: Filter Rows Using WHERE clause
SELECT * FROM ecommerce_analytics.customer_orders WHERE payment_method = 'credit_card';
Returns orders paid via credit card.
krishna :) SELECT * FROM ecommerce_analytics.customer_orders WHERE payment_method = 'credit_card'; SELECT * FROM ecommerce_analytics.customer_orders WHERE payment_method = 'credit_card' Query id: 003a74d6-b8b2-4926-892d-cefca9f25244 ┌─order_id─┬─customer_id─┬─order_date─┬─order_amount─┬─payment_method─┐ 1. │ 1001 │ 201 │ 2025-05-01 │ 259.99 │ credit_card │ └──────────┴─────────────┴────────────┴──────────────┴────────────────┘ 1 row in set. Elapsed: 0.003 sec.
Example 4: Sort Results
SELECT * FROM ecommerce_analytics.customer_orders ORDER BY order_amount DESC;
Displays orders sorted by highest to lowest amount.
krishna :) SELECT * FROM ecommerce_analytics.customer_orders ORDER BY order_amount DESC; SELECT * FROM ecommerce_analytics.customer_orders ORDER BY order_amount DESC Query id: faada74e-ea31-48dd-9d37-a42dddc1250a ┌─order_id─┬─customer_id─┬─order_date─┬─order_amount─┬─payment_method─┐ 1. │ 1001 │ 201 │ 2025-05-01 │ 259.99 │ credit_card │ 2. │ 1003 │ 203 │ 2025-05-02 │ 145 │ net_banking │ 3. │ 1002 │ 202 │ 2025-05-02 │ 89.5 │ paypal │ └──────────┴─────────────┴────────────┴──────────────┴────────────────┘ 3 rows in set. Elapsed: 0.005 sec.
Example 5: Limit Results
SELECT * FROM ecommerce_analytics.customer_orders LIMIT 2;
Returns only the first 2 rows (based on table’s internal order).
krishna :) SELECT * FROM ecommerce_analytics.customer_orders LIMIT 2; SELECT * FROM ecommerce_analytics.customer_orders LIMIT 2 Query id: 0c816572-3363-45c1-9a7f-5990743d0328 ┌─order_id─┬─customer_id─┬─order_date─┬─order_amount─┬─payment_method─┐ 1. │ 1001 │ 201 │ 2025-05-01 │ 259.99 │ credit_card │ 2. │ 1002 │ 202 │ 2025-05-02 │ 89.5 │ paypal │ └──────────┴─────────────┴────────────┴──────────────┴────────────────┘ 2 rows in set. Elapsed: 0.003 sec.
5. Drop a table
In ClickHouse, the DROP TABLE statement completely deletes the table and all its data from disk. Once dropped, the data is not recoverable, so use this command with caution, especially in production environments.
Syntax
DROP TABLE [IF EXISTS] database_name.table_name;
IF EXISTS prevents an error if the table doesn't exist.
Example: Drop the customer_orders Table
DROP TABLE IF EXISTS ecommerce_analytics.customer_orders;
This command deletes the customer_orders table from the ecommerce_analytics database.
krishna :) DROP TABLE IF EXISTS ecommerce_analytics.customer_orders; DROP TABLE IF EXISTS ecommerce_analytics.customer_orders Query id: b0de77ff-39f9-4749-a300-751745eb47e7 Ok. 0 rows in set. Elapsed: 0.006 sec. krishna :) krishna :) SHOW TABLES FROM ecommerce_analytics; SHOW TABLES FROM ecommerce_analytics Query id: 511c93af-834c-4302-a66b-c1c644f263e3 Ok. 0 rows in set. Elapsed: 0.003 sec. krishna :)
6. Drop a database
The DROP DATABASE statement completely deletes a database and all its tables, along with any data contained within them. Like the DROP TABLE command, this operation is permanent and cannot be undone. Be extra cautious when using it, especially in production environments.
Syntax
DROP DATABASE [IF EXISTS] database_name;
"IF EXISTS" ensures no error is thrown if the database does not exist.
Example: Drop the ecommerce_analytics Database
DROP DATABASE IF EXISTS ecommerce_analytics;
This command will remove the entire ecommerce_analytics database and all the tables it contains.
krishna :) DROP DATABASE IF EXISTS ecommerce_analytics; DROP DATABASE IF EXISTS ecommerce_analytics Query id: d0f13331-ae26-476e-865d-c2f2a7dec13e Ok. 0 rows in set. Elapsed: 0.006 sec. krishna :) krishna :) SHOW DATABASES; SHOW DATABASES Query id: 8de65e97-f1ab-47a0-9f56-9721209d48c8 ┌─name───────────────┐ 1. │ INFORMATION_SCHEMA │ 2. │ default │ 3. │ information_schema │ 4. │ system │ └────────────────────┘ 4 rows in set. Elapsed: 0.003 sec. krishna :)
In this guide, we've covered the essential steps to get started with ClickHouse, one of the fastest columnar databases designed for real-time analytics. You’ve learned how to:
· Create a Database: We discussed how to create a database to organize your tables effectively.
· Create Tables: You learned how to define tables with meaningful column types and choose the right storage engine (like MergeTree) for optimized performance.
· Insert Data: We demonstrated how to insert data efficiently into your tables and explained best practices for bulk inserts.
· Query Data: You explored how to retrieve data using SELECT statements, filter with WHERE, sort results, and limit rows.
· Drop Tables and Databases: Finally, we covered how to delete tables and databases when you no longer need them, ensuring your data management stays clean.
ClickHouse’s simplicity, combined with its ability to handle massive data volumes, makes it a great choice for analytical applications. Whether you're building a small project or scaling to large-scale analytics, these foundational steps will give you the tools to manage your data efficiently.
Previous Next Home
No comments:
Post a Comment