ClickHouse client cheatsheet

clickhouse-cheatsheet.md

Cheatseet assumes you're just playing around on a not clustered ClickHouse server.

docker run -it yandex/clickhouse-client --host your.toy.server.host --port 9500 --user default --password password123 --multiline

SHOW DATABASES

SHOW TABLES (in current database)

SHOW DICTIONARIES (in current database)

SET send_logs_level='trace';

SELECT name, engine FROM system.databases;

SELECT name, engine FROM system.tables WHERE database = 'default';

SELECT name, type FROM system.columns WHERE database = 'default' AND table = 'my_table';

OR

DESCRIBE TABLE my_table;

BTW if you want to see the columns list a wide table you also could do this: SELECT * from mydb.wide_table LIMIT 1 FORMAT Vertical;

CREATE DATABASE mydb;

SELECT database, name, type, key, query_count FROM system.dictionaries;

SELECT name, status FROM system.dictionaries; // statuses: Enum8('NOT_LOADED' = 0, 'LOADED' = 1, 'FAILED' = 2, 'LOADING' = 3, 'FAILED_AND_RELOADING' = 4, 'LOADED_AND_RELOADING' = 5, 'NOT_EXIST' = 6)

SELECT *
  FROM system.dictionaries 
 WHERE name = 'users'
FORMAT Vertical

SELECT 
  formatReadableSize(sum(data_compressed_bytes)) AS compressed,
  formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.columns WHERE table = 'test';

SELECT name, formatReadableSize(bytes_allocated) AS human_size, bytes_allocated FROM system.dictionaries ORDER BY bytes_allocated DESC;

CREATE TABLE mydb.visits 
(
  app_name String,
  customer_id UUID,
  session_id UUID,
  duration Int32,
  timestamp Int32,
  event_date Date
)
ENGINE = Null();

CREATE TABLE mydb.app_daily_visits 
(
  app_name String,
  day Date,
  duration int32
)

ENGINE = AggregatingMergeTree()
PARTITION BY day
ORDER BY (app_name, day);

CREATE MATERIALIZED VIEW mydb.app_dayly_visits_handler
TO mydb.app_dayly_visits (
  `app_name` String,
  `day` Date,
  `duration` int32
) AS (
  SELECT
    app_name,
    day,
    sum(duration)
  FROM mydb.visits
);

INSERT INTO mydb.sessions_rich_metadata
  (
    session_uid,
    requested_at,
    resource_uid,
    resource_type,
    resource_name
  )
SELECT
  session_uid,
  requested_at,
  resource_uid,
  resource_type,
  multiIf
    (
      resource_type = 'movie',
      dictGetString('mydb_movies', 'name', tuple(upper(sessions_metadata.resource_uid))),
      resource_type = 'episode',
      dictGetString('mydb_series', 'name', tuple(upper(sessions_metadata.resource_uid))),
      resource_type = 'channel',
      dictGetString('mydb_channels', 'name', tuple(upper(sessions_metadata.resource_uid))),
      ''
    ) AS resource_name
FROM mydb.sessions_metadata
WHERE mydb.sessions_metadata.requested_at > 1586423119;