ClickHouse常用操作

前言

该文章介绍ClickHouse数据库常用操作。（ClickHouse有些语法和函数，与MySQL相同甚至相似）

1 查看ClickHouse数据

1.1 环境信息

1.2 登录ClickHouse

[ root@clickhouse001:~ ]# clickhouse-client --port=9000 --multiline
ClickHouse client version 21.12.3.32 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.

clickhouse001 :) 

官方文档SQL参考：https://clickhouse.com/docs/zh/sql-reference/

以下CREATE 操作均为ClickHouse单实例场景，如果在集群操作，可加上 ON CLUSTER cluster。

1.3 建库表

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

CREATE DATABASE clicktail;

CREATE TABLE clicktail.mysql_slow_log_ali
(
    `_time` DateTime DEFAULT now(),
    `_date` Date DEFAULT toDate(_time),
    `InstanceId` String,
    `InstanceName` String,
    `InstanceType` String,
    `HostAddress` String,
    `QueryTimes` Int32,
    `SQLText` String,
    `QueryTimeMS` Int32,
    `LockTimes` Int32,
    `ExecutionStartTime` String,
    `ReturnRowCounts` Int32,
    `ParseRowCounts` Int32,
    `DBName` String
)
ENGINE = MergeTree(_date, (_time, SQLText), 8192)
COMMENT '阿里云慢查询记录表';

1.4 查看库表

clickhouse001 :) show databases;

SHOW DATABASES

Query id: 273dfaef-c5ce-4a5e-9a70-718483c51302

┌─name───────────────┐
│ INFORMATION_SCHEMA                 │
│ clicktail                          │
│ dba_cmdb                           │
│ default                            │
│ heartbeat                          │
│ information_schema                 │
│ system                             │
│ test                               │
└──────────────────┘

10 rows in set. Elapsed: 0.005 sec. 

clickhouse001 :) use clicktail;

USE clicktail

Query id: 992391f4-439d-44d1-adf6-182a591584da

Ok.

0 rows in set. Elapsed: 0.003 sec. 

clickhouse001 :) show tables;

SHOW TABLES

Query id: 59f192e5-de79-4140-88c3-21ec6d4e5181

┌─name────────────────────┐
│ mysql_audit_log                              │
│ mysql_audit_log_ali                          │
│ mysql_audit_log_percona                      │
│ mysql_slow_log                               │
│ mysql_slow_log_ali                           │
│ mysql_slow_log_cdb                           │
│ slow_log_linshi                              │
│ test                                         │
│ test02                                       │
│ test03                                       │
│ test04                                       │
└───────────────────────┘

11 rows in set. Elapsed: 0.003 sec. 

1.5 创建用户

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
        [, name2 [ON CLUSTER cluster_name2] ...]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [DEFAULT DATABASE database | NONE]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

CREATE USER u_slowlog_r IDENTIFIED BY 'xxxxxxxx';
GRANT SELECT ON clicktail.mysql_slow_log TO u_slowlog_r;

1.6 查看用户

clickhouse001 :) SHOW CREATE USER u_slowlog_r;

SHOW CREATE USER u_slowlog_r

Query id: f96e892f-7496-4379-9622-3fa07ec8ae15

┌─────────────CREATE USER u_slowlog_r─────────────────────┐
│ CREATE USER u_slowlog_r IDENTIFIED WITH sha256_password                                   │
└──────────────────────────── ─────────────────┘

1 rows in set. Elapsed: 0.004 sec. 

clickhouse001 :) SHOW GRANTS FOR u_slowlog_r;

SHOW GRANTS FOR u_slowlog_r

Query id: ac2c5dc7-d8d5-4d3d-a7b2-f65e10236eaa

┌─GRANTS FOR u_slowlog_r───────────────────────────────────────┐
│ GRANT SELECT ON clicktail.mysql_slow_log TO u_slowlog_r                                              │
│ GRANT SELECT ON clicktail.mysql_slow_log_ali TO u_slowlog_r                                          │
│ GRANT SELECT ON clicktail.mysql_slow_log_cdb TO u_slowlog_r                                          │
└───────────────────────────────────────────────────┘

10 rows in set. Elapsed: 0.002 sec. 

clickhouse001 :) SELECT name,host_ip,default_roles_all FROM system.users WHERE name='u_slowlog_r';

SELECT
    name,
    host_ip,
    default_roles_all
FROM system.users
WHERE name = 'u_slowlog_r'

Query id: 89f532c3-85a6-4d2c-98ac-7be1265e426d

┌─name────────┬─host_ip──┬─default_roles_all─┐
│ u_slowlog_r          │ ['::/0']    │                 1   │
└───────────┴────── ┴───────── ─┘

1 rows in set. Elapsed: 0.001 sec. 

1.7 kill会话

KILL QUERY [ON CLUSTER cluster]
  WHERE <where expression to SELECT FROM system.processes query>
  [SYNC|ASYNC|TEST]
  [FORMAT format]

clickhouse001 :) SELECT query_id, query,user,address,current_database,elapsed FROM system.processes\G

SELECT
    query_id,
    query,
    user,
    address,
    current_database,
    elapsed
FROM system.processes

Query id: 9454ae26-3310-493f-9bdc-e0e076084893

Row 1:
──────
query_id:         9454ae26-3310-493f-9bdc-e0e076084893
query:            SELECT query_id, query,user,address,current_database,elapsed FROM system.processes
user:             default
address:          ::ffff:127.0.0.1
current_database: system
elapsed:          0.002582042

1 rows in set. Elapsed: 0.004 sec. 

-- Forcibly terminates all queries with the specified query_id:
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90'  --上面SELECT查到query_id

-- Synchronously terminates all queries run by 'username':
KILL QUERY WHERE user='username' SYNC

1.8 查看库表空间统计信息

SELECT database AS `库名`,
    table AS `表名`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
GROUP BY database,table
order by sum(data_uncompressed_bytes) desc;