Write, Run & Share ClickHouse queries online using OneCompiler's ClickHouse online editor for free. It's one of the robust, feature-rich online editors for ClickHouse. Getting started with the OneCompiler's ClickHouse editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'ClickHouse' and start writing queries to learn and test online without worrying about tedious process of installation.
ClickHouse is an open-source, column-oriented OLAP (Online Analytical Processing) database management system developed by Yandex. It is designed for high-performance analytics and can process billions of rows in real-time. ClickHouse uses SQL syntax and is optimized for read-heavy analytical workloads.
Creates a new table with specified columns and data types. ClickHouse requires a table engine to be specified which determines how data is stored and accessed. Use the Memory engine for testing and development as it stores data in RAM. For production workloads, use MergeTree engine which provides persistent storage with efficient sorting and indexing capabilities.
CREATE TABLE EMPLOYEE (
empId Int32,
name String,
dept String
) ENGINE = Memory;
Adds new rows to a table. You can insert single rows or multiple rows in a single statement for better performance. ClickHouse is optimized for batch inserts - inserting thousands of rows at once is much more efficient than inserting one row at a time. When loading large datasets, consider using batch inserts or the native format for maximum throughput.
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');
-- Insert multiple rows
INSERT INTO EMPLOYEE VALUES (4, 'Bob', 'Engineering'), (5, 'Eve', 'HR');
Retrieves data from tables with support for filtering, ordering, and limiting results. ClickHouse is optimized for fast analytical queries and can process complex SELECT statements on billions of rows in seconds. The columnar storage format means that queries only read the columns they need, making analytical queries extremely efficient even on very wide tables with hundreds of columns.
-- Select all
SELECT * FROM EMPLOYEE;
-- Select with condition
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
-- Select specific columns
SELECT name, dept FROM EMPLOYEE;
-- Order by
SELECT * FROM EMPLOYEE ORDER BY name ASC;
-- Limit results
SELECT * FROM EMPLOYEE LIMIT 10;
Modifies existing data in a table. Unlike standard SQL, ClickHouse uses ALTER TABLE syntax for updates because it's designed for analytics rather than transactional workloads. This operation rewrites affected data parts on disk, so frequent updates are not recommended. For data that changes often, consider using ReplacingMergeTree or designing your schema to append new versions instead of updating.
ALTER TABLE EMPLOYEE UPDATE dept = 'Marketing' WHERE empId = 1;
Removes rows from a table using ALTER TABLE syntax. Similar to UPDATE, this operation rewrites data parts and is not optimized for frequent use. For time-series data or logs, consider using TTL (Time To Live) to automatically expire old data instead of manual deletes. ClickHouse is designed for append-heavy workloads where deletes are rare.
ALTER TABLE EMPLOYEE DELETE WHERE empId = 1;
ClickHouse excels at analytical queries with aggregations, which is its primary use case. Use aggregate functions like SUM, AVG, COUNT, MIN, MAX with GROUP BY to analyze large datasets in real-time. These operations are highly optimized for columnar storage - ClickHouse can aggregate billions of rows per second on a single server. The query engine uses vectorized execution and SIMD instructions for maximum performance.
CREATE TABLE sales (
product String,
amount Float64,
quantity UInt32
) ENGINE = Memory;
INSERT INTO sales VALUES ('Apple', 10.5, 5), ('Banana', 5.0, 10), ('Apple', 15.0, 3), ('Orange', 8.0, 7);
-- Group by with aggregates
SELECT
product,
SUM(amount) AS total_amount,
SUM(quantity) AS total_qty,
AVG(amount) AS avg_amount
FROM sales
GROUP BY product
ORDER BY total_amount DESC;
-- Count
SELECT COUNT(*) FROM sales;
-- Min/Max
SELECT MIN(amount), MAX(amount) FROM sales;
Arrays allow storing multiple values of the same type in a single column, which is useful for tags, categories, or denormalized data. Use the arrayJoin function to expand array elements into separate rows, which is powerful for unnesting and analyzing array data. ClickHouse also provides many array functions like arrayLength, arrayElement, arrayFilter, and arrayMap for working with array data efficiently.
CREATE TABLE user_tags (
user_id UInt32,
tags Array(String)
) ENGINE = Memory;
INSERT INTO user_tags VALUES (1, ['developer', 'blogger']);
INSERT INTO user_tags VALUES (2, ['designer', 'artist']);
SELECT * FROM user_tags;
-- Expand array into rows
SELECT user_id, arrayJoin(tags) AS tag FROM user_tags;
-- Array functions
SELECT arrayLength(tags) FROM user_tags;
ClickHouse has built-in Date and DateTime types with powerful functions for extraction and manipulation. Date functions are optimized for time-series analytics, making it easy to group and filter data by time periods like hours, days, weeks, or months. This makes ClickHouse excellent for analyzing logs, events, metrics, and any time-stamped data where you need to aggregate by time windows.
CREATE TABLE events (
event_date Date,
event_time DateTime,
event_type String
) ENGINE = Memory;
INSERT INTO events VALUES ('2024-01-15', '2024-01-15 10:30:00', 'click');
INSERT INTO events VALUES ('2024-01-16', '2024-01-16 14:45:00', 'view');
SELECT * FROM events;
-- Date functions
SELECT
event_date,
toYear(event_date) AS year,
toMonth(event_date) AS month,
toDayOfWeek(event_date) AS day_of_week
FROM events;
-- Current date/time
SELECT now(), today();
ClickHouse provides a rich set of string manipulation functions for text processing and analysis. These functions are vectorized for high performance and can process millions of strings per second. Common operations include case conversion, substring extraction, concatenation, and pattern matching. ClickHouse also supports regular expressions and full-text search capabilities for more advanced text analysis.
SELECT
lower('HELLO') AS lowercase,
upper('hello') AS uppercase,
length('hello') AS len,
concat('Hello', ' ', 'World') AS combined,
substring('Hello World', 1, 5) AS sub;
Use CASE expressions or the IF function for conditional logic in queries. These are useful for categorizing data into buckets, handling NULL values gracefully, or creating computed columns based on complex conditions. You can nest multiple conditions and combine them with other functions to build sophisticated transformations directly in your queries without needing application-side logic.
-- CASE expression
SELECT
name,
CASE
WHEN dept = 'Sales' THEN 'Revenue'
WHEN dept = 'Engineering' THEN 'Tech'
ELSE 'Other'
END AS category
FROM EMPLOYEE;
-- IF function
SELECT name, IF(dept = 'Sales', 'Yes', 'No') AS is_sales FROM EMPLOYEE;
ClickHouse offers different table engines optimized for different use cases. The Memory engine stores data in RAM and is perfect for testing or temporary tables - data is lost on server restart. MergeTree is the most powerful engine family for production analytics, supporting efficient sorting, partitioning, replication, and data TTL. Choose your engine based on your data persistence, query patterns, and performance requirements.
-- Memory engine (data stored in RAM, lost on restart)
CREATE TABLE temp_data (id Int32, value String) ENGINE = Memory;
-- MergeTree engine (for persistent storage with sorting)
CREATE TABLE logs (
event_date Date,
event_type String,
value Float64
) ENGINE = MergeTree()
ORDER BY event_date;
Modify table structure by adding or removing columns using ALTER TABLE commands. These operations are lightweight in ClickHouse and don't require rewriting existing data. DROP TABLE removes the table and all its data permanently. Use IF EXISTS to avoid errors when dropping tables that may not exist - this is useful in scripts and migrations.
-- Add column
ALTER TABLE EMPLOYEE ADD COLUMN email String;
-- Drop column
ALTER TABLE EMPLOYEE DROP COLUMN email;
-- Drop table
DROP TABLE IF EXISTS EMPLOYEE;
