Using Comments in ClickHouse: Single-line and Multi-line Syntax Explained

  

When writing ClickHouse SQL queries, it’s important to annotate them with comments for clarity and maintenance. ClickHouse supports the following comment styles:

 

1. Single-Line Comments (--)

Use two dashes to create a single-line comment. Anything after -- on that line will be ignored by ClickHouse.

 

singleLineComment.sql 

-- Fetching all rows from the orders table
SELECT * FROM demo_db.orders

   

Let’s execute the SQL query from a file using the ClickHouse client.

 

clickhouse-client < singleLineComment.sql

$clickhouse-client < singleLineComment.sql 
1001	Ram	250.75	2024-06-01
1002	Krishna	145	2024-06-03
1003	Chamu	320	2024-06-04

   

2. Multi line comments

If your .sql file has multiple statements, prefer /* */

 

multiLineComment.sql

/*
  This query retrieves the all the 
  orders currently available in the system
*/
SELECT * from demo_db.orders;

$clickhouse-client < multiLineComment.sql 
1001  Ram 250.75  2024-06-01
1002  Krishna 145 2024-06-03
1003  Chamu 320 2024-06-04

   

Let’s create a script (.sql) file that:

 

·      Creates a database comments_demo

·      Creates a sample table

·      Inserts data

·      Uses a mix of -- single-line and /* multi-line */ comments

 

commentsDemo.sql

 

-- Drop the database if it already exists
DROP DATABASE IF EXISTS comments_demo;

-- Create a new database for demonstration
CREATE DATABASE comments_demo;

-- Switch to the new database
USE comments_demo;

/*
  Creating a sample table named 'employee'.
  This table stores employee id, name, department, and salary.
*/
CREATE TABLE employee
(
    id UInt32,
    name String,
    department String,
    salary UInt32
)
ENGINE = MergeTree()
ORDER BY id;

/*
  Inserting sample data into 'employee' table.
  We include employees from different departments
  with varied salary ranges.
*/
INSERT INTO employee VALUES
(1, 'Ram', 'Engineering', 80000),
(2, 'Krishna', 'HR', 55000),
(3, 'Chamu', 'Engineering', 95000),
(4, 'Sailu', 'Marketing', 62000),
(5, 'Gopu', 'HR', 48000);

/*
  Query 1: Select all data from the employee table.
  This verifies the initial insert and shows the table structure.
*/
SELECT * FROM employee;

/*
  Query 2: Use an IF-like CASE WHEN expression to categorize salaries.
  ClickHouse does not have a traditional IF/ELSE,
  but supports CASE WHEN and multiIf for similar logic.
*/
SELECT
    name,
    salary,
    -- Categorize salary levels using CASE WHEN logic
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employee;

/*
  Query 3: Using multiIf function to achieve the same result more concisely.
  multiIf(condition1, value1, condition2, value2, ..., default)
*/
SELECT
    name,
    salary,
    multiIf(
        salary >= 90000, 'High',
        salary >= 60000, 'Medium',
        'Low'
    ) AS salary_level
FROM employee;

/*
  Query 4: Use IF-style NULL fallback with `ifNull()` function.
  If department is null, show 'Unknown'.
  In this dataset we don't have nulls, but this is to demo syntax.
*/
SELECT
    name,
    ifNull(department, 'Unknown') AS safe_department
FROM employee;

/*
  Query 5: Use `coalesce` (returns first non-null argument) to choose from multiple fallback values.
*/
SELECT
    name,
    coalesce(NULL, NULL, department, 'Unknown') AS resolved_department
FROM employee;

  Execute following statement to execute the SQL statements from commentsDemo.sql file.

 

clickhouse-client < commentsDemo.sql

$clickhouse-client < commentsDemo.sql 
1	Ram	Engineering	80000
2	Krishna	HR	55000
3	Chamu	Engineering	95000
4	Sailu	Marketing	62000
5	Gopu	HR	48000
Ram	80000	Medium
Krishna	55000	Low
Chamu	95000	High
Sailu	62000	Medium
Gopu	48000	Low
Ram	80000	Medium
Krishna	55000	Low
Chamu	95000	High
Sailu	62000	Medium
Gopu	48000	Low
Ram	Engineering
Krishna	HR
Chamu	Engineering
Sailu	Marketing
Gopu	HR
Ram	Engineering
Krishna	HR
Chamu	Engineering
Sailu	Marketing
Gopu	HR

 

Previous                                                    Next                                                    Home