分享知识，分享快乐

0%

clickhouse运维

发表于

查看后台进程并杀死

– 这个命令和mysql是一样的

1
show processlist

– 如果进程太多，也可用通过查询系统表 processes，

1
select * from system.processes

– 指定主要关心字段

1
2
3
select 
  user,query_id,query,elapsed,memory_usage
from system.processes

杀死后台进程
– 通过上面指令获取到进程相关信息后，可以用query_id条件kill进程

1
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90'

– 杀死default用户下的所有进程

1
KILL QUERY WHERE user='default'

clickhouse启动停止服务

1
2
3
service clickhouse-server start
service clickhouse-server stop
service clickhouse-server restart

Clickhouse删除表某一天分区

方法一：

1
ALTER TABLE kuming.tableName DELETE WHERE toDate(insert_at_timestamp)='2020-07-21';

方法二：

1
ALTER TABLE kuming.tableName DELETE WHERE insert_at_timestamp<=1596470399;

方法三：（当前两种方法分区数据没有删除掉的时候可以用方法三）

1
ALTER TABLE kuming.tableName DROP PARTITION '2020-08-03';

alter table 表名 drop partition 分区名

分区名可以用下语句查询

1
select * from system.parts p where table = '表名'

查看日志

可以在clickhouse结点上查看/var/log/clickhouse-server/clickhouse-server.log，注意ERROR级别日志

– 特别主要：

可以在clickhouse结点上查看/var/log/clickhouse-server/clickhouse-server.log，注意ERROR级别日志

/var/log/clickhouse-server/clickhouse-server.err.log

clickhouse 用户权限设置

https://www.jianshu.com/p/3e08a7150fb1

创建角色和普通用户

1
2
3
4
5
6
CREATE ROLE accountant;
GRANT SELECT ON dbtest.* TO accountant;

CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';

GRANT accountant TO mira;

合并分区

1
2
3
optimize table xxoxx final;

optimize table xxoxx  partition ‘20211206’ final;

在客户端打印日志 --send_logs_level=trace

[atguigu@hadoop102 lib]$ clickhouse-client --send_logs_level=trace <<< 'select*from t_order_mt2 where total_amount > toDecimal32(900.,2)";

ReplacingMergeTree

通过测试得到结论
实际上是使用order by字段作为唯一键
去重不能跨分区
只有同—批插入(新版本)或合并分区时才会进行去重
认定重复的数据保留，版本字段值最大的
如果版本字段相同则按插入J顺序保留最后一笔
I

ClickHouse 分片双副本集群部署

参考 https://www.jianshu.com/p/5bcaad0a02b1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 创建分布式数据库

create database PAY_TRADECENTER on cluster ck_cluster;
--drop database PAY_TRADECENTER on cluster ck_cluster ;

-- 在集群的每个机器上面创建mysql镜像库
-- drop database mysql_pay_tradecenter on cluster ck_cluster ;
CREATE DATABASE mysql_pay_tradecenter on cluster ck_cluster ENGINE = MySQL('****:3306', 'PAY_TRADECENTER', 'cs_hadoop', '***') ;

-- 在集群的每个机器上面建立本地表 (设置分片)
-- drop table PAY_TRADECENTER.T_TC_BASE_LOCAL on cluster ck_cluster
create table PAY_TRADECENTER.T_TC_BASE_LOCAL on cluster ck_cluster as mysql_pay_tradecenter.T_TC_BASE  
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/T_TC_BASE_LOCAL', '{replica}') 
PARTITION BY toYYYYMM(CREATED_AT) 
primary key (ID)
ORDER BY (ID);


-- 在集群的每个机器上面建立分布式表

create table PAY_TRADECENTER.T_TC_BASE on cluster ck_cluster 
as PAY_TRADECENTER.T_TC_BASE_LOCAL
ENGINE = Distributed(ck_cluster, PAY_TRADECENTER, T_TC_BASE_LOCAL, rand());

-- 导入数据
insert into PAY_TRADECENTER.T_TC_BASE SELECT * from mysql_pay_tradecenter.T_TC_BASE

clickhouse用presto查询

测试下来clickhouse用presto查询 效率低

卸载及删除安装文件

1
2
3
4
5
6
yum list installed | grep clickhouse
yum remove -y clickhouse-common-static
yum remove -y clickhouse-server-common
rm -rf /var/lib/clickhouse
rm -rf /etc/clickhouse-*
rm -rf /var/log/clickhouse-server

备份与恢复

https://github.com/AlexAkulov/clickhouse-backup

修改备份工具配置文件的端口和密码

cp /etc/clickhouse-backup/config.yml.example /etc/clickhouse-backup/config.yml

创建备份

• 查看可用命令

1
$ clickhouse-backup help

• 显示要备份的表

1
$ clickhouse-backup tables

• 创建备份

1
$ sudo clickhouse-backup create

• 查看现有的本地备份

1
$ sudo clickhouse-backup list

- 从备份还原

1
clickhouse-backup restore 2022-01-28T06-13-35

– 恢复指定表

1
clickhouse-backup restore 2022-01-28T06-13-35 --table dbtest.abcd

Suggested Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE DATABASE test ENGINE=MaterializeMySQL('127.0.0.1:3306', 'test', 'root', 'clickhouse')
    TABLE OVERRIDE t1 (
        COLUMNS (
            -- sparse column list, replacing existing ones or adding new ones
            timestamp DateTime CODEC(DoubleDelta, Default)
            PROJECTION ...,
            CONSTRAINT ...,
            INDEX ...
        )
        -- storage parameters:
        ORDER BY expr
        PRIMARY KEY expr
        PARTITION BY expr
        SAMPLE BY expr
        TTL ...
    ),
    -- multiple tables can be overridden
    TABLE OVERRIDE t2 (
        PARTITION BY tuple(id % 10, toYYYY(created))
    )

Detailed description / Documentation draft:
The EXPLAIN TABLE OVERRIDE query can be used for pre-validating overrides. Example:

1
2
EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'table', 'user', 'pw')
    PARTITION BY tuple(toYYYYMM(created), id % 8)