ClickHouse 研讨会学习笔记(clickhouse tips and tricks)

一.显示执行日志

clickhouse-client --send_logs_level=trace

或者进入client session 后输入

set send_logs_level = 'trace'
select 1
set send_log_level='none'

可以跟踪执行日志

二.对字段进行编码

1. 创建表时声明对列编码

create table test__codecs ( a String,
a_lc LowCardinality(String) default a,
b UInt32,
b_delta UInt32 default b Codec(Delta),
b_delta_lz4 UInt32 default b Codec(Delta,LZ4),
b_dd UInt32 default b Codec(DoubleDelta),
b_dd_lz4 UInt32 default b Codec(DoubleDelta,LZ4)
)
engine = MergeTree
partiton by tuple() order by tuple();

字段 a 为原字符字段, a_lc 对 a 的字典编码类型.b 为原 int 字段,b_xxx 对 b 字段值进行不同级别的 codec

Codec 可接受两个参数,第一个指定编码方式,第二个指定压缩算法

partition by tuple() 指不分区

2. 加载数据

inert into test_codecs(a,b)
select
concat('prefix',toString(rand() * 1000)),now()+(number * 10)
from system.numbers 
limit 100000000
settings max_block_size=1000000

3. 查看数据

select name , sum(data_compressed_bytes) comp,
sum(data_uncompressed_bytes) uncomp,
round(comp/uncomp * 100.0,2) as percent
from system.columns where table = 'test_codecs'
group by name order by name

可以比较不同压缩率

4. 编码同时可加速查询

select a as a,count(*) as c from test_codecs
group by a order by c asc limit 10

select a_lc as a,count(*) as c from test_codecs
group by a order by c asc limit 10

第二个编码列的查询要快得多

5. 各种编码方式比较

三.善用物化视图

常见场景

例如展示每一个节点cpu 利用率的当前值

使用argMaxState 聚合列

create materialized view cpu_last_point_idle_mv 
engine = AggregatingMergeTree()
partition by tuple()
order by tags_id
populate
as select
argMaxState(create_date,created_at) as created_data,
maxState(create_at) as max_created_max,
argMaxState(time,created_at) as time,
tags_id,
argMaxState(usage_idle,created_at) as usage_idle
from cpu 
group by tags_id

argMax(a,b) 函数返回 b 最大值时 a的值

State 为聚合函数的后缀,聚合函数加此后缀不直接返回结果,返回聚合函数的中间结果,该中间结果可在AggregatingMergeTree 引擎中使用

使用Merge函数后缀得到聚合结果

create view cpu_last_point_idle_v as
select 
argMaxMerge(created_date) as created_date,
maxMerge(max_created_at) as created_at,
argMaxMerge(time) as time,
tags_id,
argMaxMerge(usage_idle) as usage_idle
from cpu_last_point_idle_mv
group by tags_id

查询结果视图

select 
tags_id,
100 - usage_idle usage
from cpu_last_point_idle_v
order by usage desc,tags_id asc
limit 10

可以看到,查询速度非常快

四.使用数组类型存储 k-v 对

常规的表结构

create table cpu (
    created_date Date default today(),
    created_at DateTime default now(),
    time Strng,
    tags_id UInt32,
    usage_user Float64,
    usage_system Float64,
    ...
    additional_tags String default '')
enginge = MergeTree()
partition by created_date
order by (tags_id,created_at)

使用数组字段将获得更多灵活性

create table cpu_dynamic(
    created_data Date,
    created_at DateTime,
    time String,
    tags_id UInt32,
    metrics_name Array(String),
    metrics_value Array(Float64)
)
engine = MergeTree()
partition by created_date
order by (tags_id,created_at)

metrics_name 为 key 数组 metrics_value 为 value 数组

以json串格式插入数据

clickhouse-client -d default \
--query="insert into cpu_dynamic format JSONEachRow"
<

select tags_id,name,value 
from cpu_dynamic
array join metrics_name as name,metrics_value as value 

alter table cpu_dynamic add column usage_user materialized
    metrics_value[indexOf(metrics_name,'user']
        after tags_id



select time,tags_id,usage_user 
from cpu_dynamic

select tags.rack rack,avg(100-cpu.usage_idle) usage 
from cpu 
inner join tags as t on cpu.tags_id = t.id
group by rack
order by usage desc 
limit 10

  tags
  
    localhost3306
    root****
    tsbstags
  
  
  
     id 
    
      hostnameString
      
     
    ...
  

select 
    dictGetString('tags','rack',toUInt64(cpu.tags_id)) rack,
    avg(100-cpu.usage_idle) usage
from cpu
group by rack
order by usage desc
limit 10

create database mysql_repl
engin = MySQL (
    '127.0.0.1'.
    'repl',
    'root',
    'secret'
)

select 
  t.datetime,t.date,t.request_id,
  t.name customer,s.name sku
from (
  select t.* from traffic t
  join customer c on t.customer_id=c.id ) as t
join mysql_repl.sku s on t.sku_id = s.id
where customer_id = 5 
order by t.request_id limit 10

create table traffic (
    datetime DateTime,
    date Date,
    request_id UInt64,
    cust_id UInt32,
    sku UInt32
) engine = MergeTree
partition by toYYYYMM(date)
order by(cust_id,date)
TTL datetime + INTERVAL 90 DAY

create table traffic_ttl_variable(
    date Date,
    retention_days UInt16,
    ...
) ...
TTL date + INTERVAL (retention_days * 2) DAY

create table traffic_ttl_disk(
    date Date,
    retention_days UInt16,
    ...
) ...
TTL data + INTERVAL 7 DAY to DISK 'ssd'
    data + INTERVAL 30 DAY to DISK 'hdd'
    data + INTERVAL 180 DAY DELETE