ClickHouse(一)概念、基础

ClickHouse概念

ClickHouse概述

    1.列式是数据库  2.在线分析处理(OLAP)  3.底层是C++编写
    多样化引擎、数据分区/线程级并行(耗CPU)、顺序读写。适用于固定宽表查询
    单表查询擅长，多表关联查询不擅长。

ClickHouse数据类型

    Int：Int8、Int16、Int32、Int64。UInt8、UInt16、UInt32、UInt64。
    Float：Float32、Float64
    Boolean：Int8
    Decimal：Decimal32(5)整数+小数=9位 小数5位、Decimal64(5)整数+小数=18位 小数5位
        Decimal128(5)整数+小数=38位 小数5位
    String：String、FixedString(N)添加空字节补全
    Enum：Enum8('hello'=1,'world'=2) 'String'=Int8 描述    Enum16 'String'=Int8
        插入的时候只能插入固定的字符串或者整数、select cast(x,'Int8') from table;
    日期：Date(2019-12-16) Datetime(2019-12-16 10:15:10) Datetime64(2019-12-16 10:15:10.47) 
    数组：Array(1,2) 或者 [1,2]
    空值：Nullable 使用空值会对性能产生负面影响

表引擎

    TinyLog：列文件保存在磁盘上，不支持索引、没有并发控制、保存数据量小的表(小于100万行)
    Memory：以未压缩形式保存在内存中、服务器重启会消失、数据量不大太(一亿以内)
    MergeTree：支持索引分区、其他表子引擎

引擎

MergeTree表语句+解释

        //主键索引，但是数据不强调唯一
        create table data(id Uint32,name String,time Datetime)engine=MergeTree 
        partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)  
        SETTINGS index_granularity=8192 //数据间隔  大量重读的需要调整
        //分区是分目录(列文件+索引文件+表定义文件)  一个分区一个线程  
        //order by 是分区内有序的，是必须填的。orderby 主键必须是第一个字段
        //二级索引：create table data(id Uint32,INDEX a total_amount TYPE minmax GRANULARITY 5)
        //二级索引作用：minmax GRANULARITY二级索引对于一级索引粒度的粒度(每次跳几个一级索引)
        //二级索引场景：数据大量重复，一级索引不明显的时候

TTL

        create table data(id Uint32 TTL create_time + INERVAL 10 SECONG);  字段级别
        alter table data(id Uint32) MODIFY TTL create_time + INERVAL 10 SECONG; 表级别

        create table data(id Uint32,name String,time Datetime)engine=MergeTree 
        partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)  
        TTL create_time + INERVAL 1 MONTH DELETE WHERE toDayOfWeek(d)=1

        SECONG/MINUTE/HOUR/DAY/MONTH
        create_time 不能是主键，类型必须是日期类型

储文件解释

        //分区存储：分区名称+最小分区编号+最小分区编号+合并等级(被合并的次数)
        //手动合并分区命令：optimize table XXXXX partition '20221010' final;
        bin文件：数据文件 mrk：标记文件(idx,bin桥梁作用)
        primary.idx：主键索引文件，加快查询效率
        minmax_create_time.idx：分区键的最大最小值
        checksums.txt：校验文件，用于校验各个文件的正确性。存放文件的size和hash值

ReplacingMergeTree

    性能继承MergeTree，但是多了去重功能，order by 字段作为唯一约束
    去重的时机：数据插入，合并分区去重，插入数据会默认去重一下   去重不能跨分区
    建表语句及解释：
        create table data(id Uint32,name String,time Datetime)engine=ReplacingMergeTree(create_time)
        partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)  
        //ReplacingMergeTree(create_time) create_time作为版本字段。默认保留最后一条插入

SummingMergeTree

    应用场景：不查询明细数据，只关心以维度聚合。非实时聚合，分区合并的时候聚合
    create table data(id Uint32,name String,time Datetime)engine=SummingMergeTree(score)
    partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)  
    //依据order by 的字段作为维度，聚合score   插入数据的时候默认会聚合一次

SQL样例

    insert into table_name values
    insert into table_name select * from table_name_a
    alter table_name delete where column='10' //删除数据
    alter table_name update name='zhangsan' where column='10' //修改数据
    删除和修改操作都比较重，高性能思路：
        更新的时候插入一条新数据，version+1，查询的时候version=max(version)
        删除：0表示未删除 1表示删除 查询的时候加一个条件 _sign=0
    子查询、with、支持join(但是没法使用缓存)、
    if(1,mm,nn) 1=true  nultiIf(con_1,then_1,con_2,then_2,con_3,then_3)
    group by a,b with rollup=a|a,b|all  group by a,b with cube=a|b|all|a,b   
        group by a,b with totals=a,b|all
    alter table table_name add column new_column_name String after col1;
    alter table table_name modify column new_column_name String ;
    alter table table_name drop column;

集群表

    CREATE TABLE on cluster dmp default.userpkg_all (gazj String) ENGINE = Distributed(dmp, default, userpkg, hiveHash(id))
    CREATE TABLE on cluster dmp default.userpkg_all (gazj String) 
        ENGINE = MergeTree partition by toYYYYMMDD(Datetime) order by (id,name)  //在每个节点都创建本地表
    插入数据的时候：向分布式表插入数据