clickhouse笔记 - 2

80 阅读 0 评论 53 点赞

我是靠谱客的博主满意大地，最近开发中收集的这篇文章主要介绍clickhouse笔记 - 2，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

查看集群分布式信息

select * from system.clusters;

查看数据库版本信息等

select * from system.build_options

查看正在执行的sql

select * from system.processes

将TABLE1重命名为TABLE2

rename table TABLE1 to TABLE2;

删除数据

ALTER TABLE db.test DELETE WHERE condition!=3;

修改数据

ALTER TABLE db.test UPDATE name='ccc' WHERE createTime='2020/2/13';

从mysql插入数据到clickhouse

clickhouse-client --host 192.168.1.155 --password default --database=DB-q "INSERT INTO historysignal (ROOMID, ROOMNAME, SAMPLERID, SAMPLERNAME, EQUIPMENTID, EQUIPMENTNAME, SIGNALID, SIGNALNAME, RECORDTIME, FLOATVALUE, STRINGVALUE, MEANINGS, STATIONNAME, STATIONID, UNIT, UNIQUID, RECORDTIMETOMIN, RECORDTIMEINORDER, ESNAME) select * from mysql('192.168.1.61:3306', 'DB', 'TABLE', 'mysql', 'mysql')"

当前连接数(分为 TCP 和 HTTP )

SELECT *
FROM system.metrics
WHERE metric LIKE '%Connection'

当前正在执行的查询

SELECT
    query_id,
    user,
    address,
    query
FROM system.processes
ORDER BY query_id ASC

查询Mutation操作(ALTER DELETE 和 ALTER UPDATE)

SELECT
    database,
    table,
    mutation_id,
    command,
    create_time,
    is_done
FROM system.mutations

终止语句

KILL QUERY WHERE query_id = 'query_id';
KILL MUTATION WHERE mutation_id = 'mutation_id';

存储空间统计,查询 CH 各个存储路径的空间

SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks

各数据库占用空间统计

SELECT
database,
formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
GROUP BY database

个列字段占用空间统计
每个列字段的压缩大小、压缩比率以及该列的每行数据大小的占比

SELECT
    database,
    table,
    column,
    any(type),
    sum(column_data_compressed_bytes) AS compressed,
    sum(column_data_uncompressed_bytes) AS uncompressed,
    round(uncompressed / compressed, 2) AS ratio,
    compressed / sum(rows) AS bpr,
    sum(rows)
FROM system.parts_columns
WHERE active AND database != 'system'
GROUP BY
    database,
    table,
    column
ORDER BY
    database ASC,
    table ASC,
    column ASC

慢查询

SELECT
    user,
    client_hostname AS host,
    client_name AS client,
    formatDateTime(query_start_time, '%T') AS started,
    query_duration_ms / 1000 AS sec,
    round(memory_usage / 1048576) AS MEM_MB,
    result_rows AS RES_CNT,
    result_bytes / 1048576 AS RES_MB,
    read_rows AS R_CNT,
    round(read_bytes / 1048576) AS R_MB,
    written_rows AS W_CNT,
    round(written_bytes / 1048576) AS W_MB,
    query
FROM system.query_log
WHERE type = 2
ORDER BY query_duration_ms DESC
LIMIT 10

副本预警监控
通过下面的 SQL 语句对副本进行预警监控，其中各个预警的变量可以根据自身情况调整。

SELECT database, table, is_leader, total_replicas, active_replicas
FROM system.replicas
WHERE is_readonly
    OR is_session_expired
    OR future_parts > 30
    OR parts_to_check > 20
    OR queue_size > 30
    OR inserts_in_queue > 20
    OR log_max_index - log_pointer > 20
    OR total_replicas < 2
    OR active_replicas < total_replicas

原文链接：字符串替换函数 | ClickHouse Docs