Clickhouse 101

calendar Dec 2, 2023 · 3 min read · clickhouse observability database  ·
Share on: linkedin copy

Clickhouse container

❯ docker pull clickhouse/clickhouse-server:25.2.1

❯ docker images
REPOSITORY                     TAG                 IMAGE ID       CREATED         SIZE
clickhouse/clickhouse-server   25.2.1              15f846e024cc   2 days ago      676MB

Server

docker run -d --name clh-server \
  --ulimit nofile=262144:262144 \
  -e CLICKHOUSE_USER=myuser \
  -e CLICKHOUSE_PASSWORD=mypwd \
  -p 18123:8123 \
  clickhouse/clickhouse-server:25.2.1

  # alternatively mount these or more...
  -v "$PWD/ch_data:/var/lib/clickhouse/" \
  -v "$PWD/ch_logs:/var/log/clickhouse-server/"
  • By default, ClickHouse will be accessible only via the Docker network. See the networking section below.
  • By default, starting above server instance will be run as the default user without password.
  • The container exposes ports:
    • 8123 for the HTTP interface⁠
    • 9000 for the native client⁠

Client

docker exec -it clh-server clickhouse-client

ClickHouse CLI

ee90a0d2dd2a :) show databases
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ system             │
   └────────────────────┘

ee90a0d2dd2a :) use system
ee90a0d2dd2a :) show tables
     ┌─name───────────────────────────┐
  1. │ aggregate_function_combinators │
  2. │ asynchronous_inserts           │
  3. │ asynchronous_loader            │
...

0c0f4f4a650d :) DESCRIBE TABLE hackernews
    ┌─name────────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
 1. │ id          │ Nullable(Int64)        │              │                    │         │                  │                │
 2. │ deleted     │ Nullable(Int64)        │              │                    │         │                  │                │
...

) CREATE DATABASE electricity;
) CREATE TABLE electricity.consumption;
) SELECT COUNT() FROM electricity.consumption;
) SELECT * FROM electricity.consumption LIMIT 5;
) SELECT * FROM electricity.consumption WHERE x=1;

ClickHouse SQL

  • ClickHouse SQL is case insensitive
  • Any number of space symbols (space, tab, line feed, CR, and form feed) are allowed in a query, including at the beginning and the end of a query
  • SQL statement may have the following elements:
    • Keywords - consist of words that are part of programming syntax
    • Identifiers - are the names of the database, tables, schema, or columns/aliases. cannot be a keyword unless they are enclosed by double quotes or backticks
    • Clauses - are part of the SQL statement and they are used to specify the conditions using which the data is retrieved from the table
    • Expressions - are a combination of operators, values, and functions that evaluate to a Boolean, numeric, or date value
    • Queries - read and return data from the database based on the criteria specified
    • Statements - act upon the data stored in the database tables and can have a persistent effect on the data
    • Comments -

Docs

KnowledgeBase

Links

Other