clickhouse hive 数据类型对应关系 hive clickhouse对比

一.clickhouse-简介

ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System) , 主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。 ClickHouse的全称是Click Stream，Data WareHouse，简称ClickHouse

1.优点

1. 灵活的MPP架构,支持线性扩展，简单方便，高可靠性
2. 多服务器分布式处理数据 ,完备的DBMS系统
3. 底层数据列式存储,支持压缩,优化数据存储,优化索引数据 优化底层存储
4. 容错跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍,其可处理的数据级别已达到10亿级别
5. 功能多：支持数据统计分析各种场景，支持类SQL查询，异地复制部署
   海量数据存储,分布式运算,快速闪电的性能,几乎实时的数据分析 ,友好的SQL语法,出色的函数支持

2.缺点

1. 不支持事务，不支持真正的删除/更新 (批量)
2. 不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下
3. 不支持二级索引
4. 不擅长多表join *** 大宽表
5. 元数据管理需要人为干预 ***
6. 尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作

二.安装

sudo yum -y install yum-utils
sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
sudo yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/clickhouse.repo
sudo yum -y install clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server start   启动服务
clickhouse-client   进入客户端

开启远程连接

cd /etc/clickhouse-server  配置文件目录
 vi config.xml  修改配置文件
 :set nu   复制156行 或者放开注释 粘贴
 <listen_host>::</listen_host>
 sudo /etc/init.d/clickhouse-server restart 重启服务

三.常用目录

数据存储目录 /var/lib/clickhouse

**服务端的配置文件目录 ** /etc/clickhouse-server

日志存储目录 /var/log/clickhouse-server

四.常用数据类型

-- 整数
UInt8 [0-255]
UInt32[0-4294967295]
-- 小数
Float32
Float64
-- 更高精准运算
Decimal32  -- 小数点后9位
-- 字符串
String
FixedString() -- 有长度限制
-- 日期类型
Date 		-- 年月日
DateTime    -- 年月日时分秒
DateTime64  --带毫秒
-- UUID类型

-- 枚举类型
Enum('女'=1,'男'=2) -- 限制插入的数据
-- 数组集合元组
Array() -- 数组 
Map()  -- 集合
Tuple() -- 元组
-- IPv4 类型 可以对ip地址校验
IPv4

五.视图

普通视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储**，而普通视图只是一层简单的查询代理**

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。

create view  test3_view as select id , upper(name) , role from tb_test3 ;
┌─name────────────┐
│ tb_test3        │
│ test3_view      │
│ test_partition1 │
│ test_partition2 │
│ test_rep        │
│ tt1             │
└─────────────────┘
drop view test3_view ;   -- 删除视图

物化视图

物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示

create materialized view mv_log engine=Log populate as select * from log ;

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。

-- 创建物化视图 指定引擎是AggregatingMergeTree()
create materialized view detail_table_view
engine=AggregatingMergeTree() -- 指定引擎
PARTITION BY toDate(ctime)  -- 指定分区字段
order by id  -- 指定排序字段
populate -- 同步数据
as select id,ctime ,sumState(money) as moneys
from detail_table group by id,ctime;
-- 查询数据
select id ,ctime,sumMerge(moneys)
from detail_table_view 
group by id,ctime;

2,2021-08-07,400
1,2021-08-06,200
-- 向明细表中插入数据
INSERT INTO detail_table VALUES(3, '2021-09-01', 500);
INSERT INTO detail_table VALUES(3, '2021-09-01', 500);
INSERT INTO detail_table VALUES(4, '2021-09-01', 400);
INSERT INTO detail_table VALUES(4, '2021-09-01', 400);
-- 再次查询物化视图 发现数据自动同步更新了
1,2021-08-06,200
2,2021-08-07,400
3,2021-09-01,1000
4,2021-09-01,800

注意

-- 删除源表中的数据 , 物化视图中的数据 不会变化   ****  
注意: 数据删除语法只适用于MergeTree引擎的表   基本语法如下
ALTER TABLE db_name.table_name DROP PARTITION '20210601'
ALTER TABLE db_name.table_name DELETE WHERE day = '20210618'
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>

六.引擎

引擎:数据库引擎  表引擎
引擎不同 数据存储的的位置不同 特点不同 操作不同
数据库引擎:创建数据库的时候可以不指定引擎  有默认的引擎

1.Log系列引擎

Log家族具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

1)TinyLog引擎

最简单的表引擎,用于将数据存储在磁盘上.每列都存储在单独的压缩文件中,写入时,数据将附加到文件末尾.该引擎没有并发控制

-- 建表
create table t_tinylog(
id UInt8,
name String
)engine=TinyLog;
-- 插入数据
insert into t_tinylog values(1,'张三'),(2,'李四');

数据底层存储 每次存储数据都是追加写入

-rw-r-----. 1 clickhouse clickhouse 28 Sep 27 04:04 id.bin
-rw-r-----. 1 clickhouse clickhouse 40 Sep 27 04:04 name.bin
-rw-r-----. 1 clickhouse clickhouse 64 Sep 27 04:04 sizes.json

2)StripeLog引擎

data.bin 存储所有数据
index.mrk 对数据建立索引 数据块标记
size.json 数据大小
并发读写

-- 建表 
create table t_stripelog(
id UInt8,
name String
)engine=StripeLog;
-- 插入数据
insert into t_stripelog values(1,'张三'),(2,'李四');

数据底层存储

-rw-r-----. 1 clickhouse clickhouse 118 Sep 27 04:16 data.bin
-rw-r-----. 1 clickhouse clickhouse  63 Sep 27 04:16 index.mrk
-rw-r-----. 1 clickhouse clickhouse  68 Sep 27 04:16 sizes.json

3)Log引擎

*.bin 存储每个字段的数据
mark.mrk 数据块标记
支持多线程处理
并发读写

-- 删除表
drop table if exists t_log;
-- 创建表
create table t_log(
id UInt8,
name String
)engine=Log;
-- 插入数据
insert into t_log values(1,'张三'),(2,'李四');

数据底层存储

-rw-r-----. 1 clickhouse clickhouse 28 Sep 27 04:22 id.bin
-rw-r-----. 1 clickhouse clickhouse 32 Sep 27 04:22 __marks.mrk
-rw-r-----. 1 clickhouse clickhouse 40 Sep 27 04:22 name.bin
-rw-r-----. 1 clickhouse clickhouse 94 Sep 27 04:22 sizes.json

2.MergeTree系列引擎

MergeTree系列的表引擎是ClickHouse数据存储功能的核心。它们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引，辅助数据跳过索引等。
1)存储按主键排序的数据。
2)如果指定了[分区键]则可以使用[分区)。
3)数据复制支持
ReplicatedMergeTree表族提供数据复制.
4)数据采样支持
如有必要，可以在表中设置数据采样方法。
5)可以合并数据,自动合并,也可手动合并

1)MergeTree引擎 主键不去重

MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，****定期合并这些数据片段****，属于相同分区的数据片段会被合成 一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。

1)建表时,要么指定主键,要么order by 设置排序字段(在没有设置主键时,排序字段就是主键)
2)与mysql不同,这里的主键没有唯一约束 不会去重

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster1](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) 
ENGINE = MergeTree() -- 指定引擎
ORDER BY expr        -- 排序
[PARTITION BY expr]  -- 分区
[PRIMARY KEY expr]   -- 主键
[SAMPLE BY expr]  
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...][SETTINGS name=value, ...] -- 过期时间

创建表

-- 删除表
drop table if exists t_merge_tree;
-- 创建表
create table t_merge_tree(
id UInt8,
name String,
gender Enum('男'=1,'女'=2,'人妖'=3)
)engine=MergeTree  -- 指定引擎
partition by gender -- 指定分区
order by id ;  -- 指定排序 默认为主键
-- 插入数据
insert into t_merge_tree values
(1,'张三','男'),
(2,'李四','男'),
(3,'王五','女'),
(4,'小超','人妖');
insert into t_merge_tree values
(5,'段总','人妖'),
(6,'大朗','男'),
(7,'金莲','女'),
(8,'西门庆','人妖');

合并数据

-- 合并前的数据
┌─id─┬─name─┬─gender─┐
│  4 │ 小超 │ 人妖   │
└────┴──────┴────────┘
┌─id─┬─name───┬─gender─┐
│  5 │ 段总   │ 人妖   │
│  8 │ 西门庆 │ 人妖   │
└────┴────────┴────────┘
-- 合并数据
optimize table t_merge_tree final;
-- 合并后的数据
┌─id─┬─name───┬─gender─┐
│  4 │ 小超   │ 人妖   │
│  5 │ 段总   │ 人妖   │
│  8 │ 西门庆 │ 人妖   │
└────┴────────┴────────┘
┌─id─┬─name─┬─gender─┐
│  3 │ 王五 │ 女     │
│  7 │ 金莲 │ 女     │
└────┴──────┴────────┘
┌─id─┬─name─┬─gender─┐
│  1 │ 张三 │ 男     │
│  2 │ 李四 │ 男     │
│  6 │ 大朗 │ 男     │
└────┴──────┴────────┘

2)ReplacingMergeTree引擎 主键去重

1)区内去重 排序  向桶的数据去重
2)相同分区内 排序字段相同的数据去重
3)会在合并的时候去重   默认5条数据自动合并一次
4)也可以手动合并 optimize table 表名 final ;

无版本

保留下来的数据 不确定  可能不是最新的数据
根据数据的插入时间 , 后插入的数据保留

-- 创建表
drop table if  exists test_replacingMergeTree1 ;
create table test_replacingMergeTree1(
oid Int8 ,
ctime DateTime ,
cost Decimal(10,2)
)engine = ReplacingMergeTree() -- 指定引擎
order by oid -- 指定排序字段 默认为主键
partition by toDate(ctime) ;-- 指定分区字段
-- 插入数据
insert into test_replacingMergeTree1 values(3,'2021-01-01 11:11:11',30) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:14',40) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:11',10);
insert into test_replacingMergeTree1 values(2,'2021-01-01 11:11:11',20) ;
insert into test_replacingMergeTree1 values(3,'2021-01-02 11:11:11',41) ;
insert into test_replacingMergeTree1 values(4,'2021-01-02 11:11:11',10);
-- 手动合并
optimize table  test_replacingMergeTree1  final ;
-- 查看结果  保留了后插入的那个数据
1,2021-01-01 03:11:11,10
2,2021-01-01 03:11:11,20
3,2021-01-02 03:11:11,41
3,2021-01-01 03:11:11,30
4,2021-01-02 03:11:11,10

有版本

- 版本字段可以是数值
- 版本字段可以是时间
1. 如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
2. 如果设置了ver版本号，则保留同一组重复数据中ver字段取值 最大的那一行。

-- 创建有版本的表
drop table if  exists test_replacingMergeTree3 ;
create table test_replacingMergeTree3(
 oid Int8 ,
 ctime DateTime ,
 cost Decimal(10,2)
)engine = ReplacingMergeTree(ctime) -- 指定版本字段 
 order by oid
 partition by toDate(ctime) ;
-- 插入数据
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:11',10) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:12',20) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:10',30);
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:19',40) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:13',50) ;
-- 手动合并数据
optimize table test_replacingMergeTree3 final;
-- 查看数据 保留版本字段 最大的那条数据
1,2021-01-01 03:11:19,40

3)CollapsingMergeTree引擎 折叠

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数 据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行 的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标 记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合 并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。 这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般。这种直观 的比喻，想必也正是折叠合并树（CollapsingMergeTree）名称的由来，

ENGINE = CollapsingMergeTree(sign) 指定折叠字段

-- 分区内  排序相同  状态为 1  -1  删除
-- 分区内  排序相同  状态为 1  1  保留后面一条数据
-- 分区内  排序相同  状态为 -1  1  无法折叠
-- 分区内  排序相同  状态为 -1  -1  去重  保留第一个数据

-- 创建表
drop table if exists tb_cps_merge_tree1 ;
CREATE TABLE tb_cps_merge_tree1(
 user_id UInt64,
 name String,
 age UInt8,
 sign Int8
)
ENGINE = CollapsingMergeTree(sign) -- 指定折叠字段
ORDER BY user_id; -- id为主键 相同的会去重
-- 插入数据
insert into tb_cps_merge_tree1 values
(1,'xiaoluo',23,1),
(2,'xiaoyu',24,1),
(3,'xiaofeng',25,1) ;
insert into tb_cps_merge_tree1 values
(1,'xiaoluo_',23,-1),
(2,'xiaoyu_',24,-1),
(3,'xiaofeng2',25,1) ;
-- 合并
optimize table tb_cps_merge_tree1 final;
-- 查看数据 相同去重 保留后插入的数据  折叠的删除  
3,xiaofeng2,25,1

4)VersionedCollapsingMergeTree引擎 折叠+版本

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠(删除)问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键(排序)相同，且Version相同、Sign相反的行，在Compaction时会被删除。与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

-- 建表
create table t_collapsing2(
    id UInt8,
    name String,
    folding Int8,
    v UInt8
)engine=VersionedCollapsingMergeTree(folding,v) -- 指定折叠字段和版本字段
order by id;
-- 插入数据
insert into t_collapsing2 values(1,'zss',-1,1);
insert into t_collapsing2 values(1,'lss',1,1);
-- 合并数据
optimize table t_collapsing2 final;
-- 版本相同 折叠相反 会被删除 包括(-1在前 1在后)

5)SummingMergeTree引擎 数值字段求和

假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY 条件明确，且不会随意改变）。

-- 建表
drop table if exists t_sum;
create table t_sum(
    id String,
    name String,
    date Date,
    wage Float64
)engine = SummingMergeTree() -- 可以指定求和字段 
order by id;
-- 插入数据
insert into t_sum values('1','zss','2021-9-25',30000);
insert into t_sum values('1','zss','2021-9-26',30000);
insert into t_sum values('2','lss','2021-9-25',30000);
insert into t_sum values('2','lss','2021-9-26',30000);
-- 合并数据
optimize table t_sum final;
-- 在合并的时候 ,分区内, 相同排序的行数据的所有的数值字段都会求和(sum)
-- 查看数据 相同id的数据自动求和
1,zss,2021-09-25,60000
2,lss,2021-09-25,60000

6)AggregatingMergeTree引擎 预先聚合

1)AggregatingMergeTree 是SummingMergeTree的升级版
2)写入时需要使用-State语法，查询时使用-Merge语法
3)字段  AggregateFunction(聚合函数, 数据类型) ;

-- 1)建立明细表
drop table if exists detail_table;
CREATE TABLE detail_table
(id UInt8,
 ctime Date,
 money UInt64
) ENGINE = MergeTree()
PARTITION BY toDate(ctime) -- 分区字段
ORDER BY id; -- 排序字段
-- 2)插入明细数据
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);
-- 查询数据
1,2021-08-06,100
1,2021-08-06,100
2,2021-08-07,200
2,2021-08-07,200
-- 3)建立预先聚合表，
drop table if exists agg_table;
CREATE TABLE agg_table
(id UInt8,
 ctime Date,
 money AggregateFunction(sum, UInt64) -- 预先聚合字段
) ENGINE = AggregatingMergeTree() -- 指定引擎
PARTITION BY  toDate(ctime) -- 分区字段
ORDER BY id; -- 排序字段
-- 4)插入数据
insert into agg_table
select id,ctime,
       sumState(money) -- 插入语法
from detail_table
group by (id,ctime);
-- 5)查询数据
select id,ctime,
       sumMerge(money) -- 查询语法取值
from agg_table group by id,ctime ;
2,2021-08-07,400
1,2021-08-06,200

创建物化视图

-- 创建物化视图 指定引擎是AggregatingMergeTree()
create materialized view detail_table_view
engine=AggregatingMergeTree() -- 指定引擎
PARTITION BY toDate(ctime)  -- 指定分区字段
order by id  -- 指定排序字段
populate -- 同步数据
as select id,ctime ,sumState(money) as moneys
from detail_table group by id,ctime;
-- 查询数据
select id ,ctime,sumMerge(moneys)
from detail_table_view 
group by id,ctime;

2,2021-08-07,400
1,2021-08-06,200
-- 向明细表中插入数据
INSERT INTO detail_table VALUES(3, '2021-09-01', 500);
INSERT INTO detail_table VALUES(3, '2021-09-01', 500);
INSERT INTO detail_table VALUES(4, '2021-09-01', 400);
INSERT INTO detail_table VALUES(4, '2021-09-01', 400);
-- 再次查询物化视图 发现数据自动同步更新了
1,2021-08-06,200
2,2021-08-07,400
3,2021-09-01,1000
4,2021-09-01,800

3.外部存储引擎

HDFS引擎

Clickhouse可以直接从HDFS中指定的目录下加载数据 , 自己根本不存储数据, 仅仅是读取数据
ENGINE = HDFS(hdfs_uri,format) 
·hdfs_uri表示HDFS的文件存储路径； 
·format表示文件格式（指ClickHouse支持的文件格式，常见的有 CSV、TSV和JSON等）。

-- 直接加载HDFS上的数据 主流的文件格式  文本  CSV  TSV   ORC PARQUET JSONROW
-- 创建表
create table  tb_hdfs(
    uid String ,
    name String ,
    age UInt8 ,
    gender String
)engine = HDFS("hdfs://doit01:8020/csv/input/user.txt" , 'CSV') ;
-- 参数一: hdfs的文件目录
-- 参数二: 文件格式

MySQL引擎

MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询， 包括SELECT和INSERT

它的声明方式如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password');
-- 其中各参数的含义分别如下： 
-- ·host:port表示MySQL的地址和端口。 
-- ·database表示数据库的名称。 
-- ·table表示需要映射的表名称。 
-- ·user表示MySQL的用户名。 
-- ·password表示MySQL的密码。
-- 支持查询数据 
-- 支持插入数据
-- 不支持删除和更新操作

-- Mysql引擎
-- 创建表
create table  tb_mysql(
    id Int32 ,
    name String
)engine =MySQL('doit01:3306','ck','tb_test1','root','root') ;

File引擎

4.内存引擎

Memory 引擎

1)Memory表引擎直接将数据保存在内存中数据既不会被压缩也不会被格式转换，数据在内存中保存的形态与查询时看到的如出一辙。
2)当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。
3)所以在一些场合，会将Memory作为测试表使用

CREATE TABLE memory_1 ( 
id UInt64 
)ENGINE = Memory() ;

Set 引擎

Set表引擎是拥有物理存储的，数据首先会被写至内存，然后被同步到磁盘文件中。所以当服务重启时，它的数据不会丢失，当数据表被重新装载时，文件数据会再次被全量加载至内存。众所周知，在Set 数据结构中，所有元素都是唯一的。Set表引擎具有去重的能力，在数据写入的过程中，重复的数据会被自动忽略。然而Set表引擎的使用场景既特殊又有限，它虽然支持正常的INSERT写入，但并不能直接使用SELECT对其进行查询，Set表引擎只能间接作为IN查询的右侧条件被查询使用

-- 创建表
drop table if exists test_set;
create table test_set(
id Int8
)engine=Set();
-- 插入数据
insert into test_set values(1),(2),(3);
-- Set表引擎只能间接作为IN查询的右侧条件被查询使用
-- 创建一个Log表
drop table if exists  test_set_source;
CREATE TABLE test_set_source(
id Int8,
name String,
age Int8
)ENGINE = Log ;
-- 插入数据
insert into test_set_source values(1,'lss',21),(2,'ww',33),(3,'zl',11) ;
-- 查询数据
select * from test_set_source where id in test_set;
1,lss,21
2,ww,33
3,zl,11

Buffer 引擎

Buffer表引擎完全使用内存装载数据，不支持文件的持久化存储，所以当服务重启之后，表内的数据会被清空。Buffer表引擎不是为了面向查询场景而设计的，它的作用是充当缓冲区的角色。
假设有这样一种场景，我们需要将数据写入目标MergeTree表A，由于写入的并发数很高，这可能会导致MergeTree表A的合并速度慢于写入速度（因为每一次INSERT都会生成一个新的分区目录）。此时，可以引入Buffer表来缓解这类问题，将Buffer表作为数据写入的缓冲区。数据首先被写入Buffer表，当满足预设条件时，Buffer表会自动将数据刷新到目标表

ENGINE = Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes) 
参数可以分成基础参数和条件参数两类
基础参数:
- database：目标表的数据库。 
- table：目标表的名称，Buffer表内的数据会自动刷新到目标表。 
- num_layers：可以理解成线程数，以并行的方式将数据刷新到目标表，官方建议设为16。 
条件参数:
- min_time和max_time：时间条件的最小和最大值，单位为秒;
- min_rows和max_rows：数据行条件的最小和最大值； 
- min_bytes和max_bytes：数据体量条件的最小和最大值，单位为字节;
1. 如果三组条件中所有的最小阈值都已满足，则触发刷新动作； 
2. 如果三组条件中至少有一个最大阈值条件满足，则触发刷新动作；

1) 创建一个目标表
create table tb_user(uid Int8 , name String) engine=TinyLog ;
2) 创建一个缓存表
CREATE TABLE tb_user_buffer AS tb_user ENGINE = Buffer(db_doit26,tb_user, 16, 5, 60, 10, 100, 10000000, 100000000) ;
3) 向缓存表中插入数据
insert into tb_user_buffer values(1,'Yang'),(2,'Haha') ,(3,'ADA') ;
4) 等待以后查看目标表中的数据
select * from tb_user ;

七.语法

基本语法

导入数据

1 insert into 表 values(数据)   insert into 表 select 查询结果
2 cat a.txt  | clickhouse-client -q  'insert into tb_name format  filrType'
3 clickhouse-client  --format_csv_delimiter='_'  -q  'insert into tb_name format  filrType'  < cat a.txt
--format_csv_delimiter='_'  指定分隔符
filrType:文件格式 CSV TSV ......

DDL基础

目前只有MergeTree、Merge和Distributed这三类表引擎支持 ALTER修改,所以在进行alter操作的时候注意表的引擎!

MergeTree 在进行修改字段的时候不允许修改分区字段 和 主键字段

-- 查看建表语句  查看引擎
show create table 表

修改表结构

-- 添加字段
alter table 表 add column 字段名  数据类型 ;
-- 删除字段
alter table 表 drop column 字段名 ;
-- 作为一个优秀的程序员,表的字段使用注释一种良好的习惯, 所以建议大家在操作的时候使用注释来描述字段的意义
-- 修改 / 添加字段的注释  内部使用的编码默认是UTF8
alter table 表 comment column 字段名 '用户名' ;

移动表

-- 修改表名 
rename table tb_test1 to t1 ;
-- 修改多张表名
rename table tb_test2 to t2 , t1 to tt1 ;
-- 移动表到另一数据库中 
rename table 表名 to 库.表名 ;
-- 查看数据库下的所有的表 
show tables ; -- 当前使用的库
show tables from 库名 ;

设置表的默认值

-- 设置列的默认值 
create table tb_test3(
   id Int8 ,
    name String comment '用户名' ,
    role String comment '角色' default 'VIP'
)engine = Log ;

更新删除数据

只有MergeTree引擎的数据才能修改
1) 可以删除分区 重新导入
2) 可以根据条件删除数据  根据条件更新数据  alter table delete/update where

-- 删除分区数据 
alter table 表 drop  partition 'SH' ;
-- 条件删除数据
alter table 表 delete where id=3 ;  -- 一定加条件
-- 条件更新数据
alter table 表 update name='李思思'  where id=3 ;

分区操作

-- 创建分区表
create table test_partition1(
id String , 
ctime DateTime
)engine=MergeTree() 
partition by toYYYYMM(ctime)
order by (id) ;
-- 查看建表语句
show create table test_partition1;
-- 插入数据
insert into test_partition1 values(1,now()) ,(2,'2021-06-11 11:12:13') ;
-- 查看表中分区
-- ClickHouse内置了许多system系统表，用于查询自身的状态信息。 其中parts系统表专门用于查询数据表的分区信息。
SELECT 
name,   -- 文件夹
table,  -- 表
partition  -- 分区
FROM system.parts -- 系统表
WHERE table = 'test_partition1' ;
-- 删除分区 删除分区以后 , 分区中的所有的数据全部删除 
alter table test_partition1 drop partition '202109' ;
-- 复制分区  
-- 复制表结构建表
create  table  tb_y  as  tb_x ;
不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提 条件：
·两张表需要拥有相同的分区键
·它们的表结构完全相同。
ALTER TABLE  B  REPLACE PARTITION '分区名' FROM A 
-- 把test_partition1表中的分区复制到test_partition2表中
alter table test_partition2  replace  partition '202106' from  test_partition1
-- 重置分区
注意: 不能重置主键和分区字段
alter  table test_rep clear column name in partition '202105' ;
-- 卸载分区
alter table 表 detach partition '分区名' ;
-- 装载分区
alter table 表 attach partition '分区名' ;
-- 记住，一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非我们主动删除或者使用ATTACH语句重新装载

查询语法

with

-- ClickHouse支持CTE（Common Table Expression，公共表表达式），以增强查询语句的表达
SELECT pow(2, 2) -- 平方函数
┌─pow(2, 2)─┐
│         4 │
└───────────┘
SELECT pow(pow(2, 2), 2)

┌─pow(pow(2, 2), 2)─┐
│                16 │
└───────────────────┘
-- 在改用CTE的形式后，可以极大地提高语句的可读性和可维护性，
with pow(2,2) as a select pow(a,2) ;
-- with语法
with () as x
-- 创建表
CREATE TABLE t_user(
 `name` String,
 `aihao` String
)ENGINE = Memory;
insert into t_user values ('zss','旅游');
insert into t_user values ('lss','打篮球');
insert into t_user values ('ww','追剧');
--  子查询的结果必须是一条数据 
with (select name from t_user where name='zss') as x
select * from t_user where name in (x);

array join 炸裂

ARRAY JOIN子句允许在数据表的内部，与数组或嵌套类型的字段进行JOIN操作，从而将一行数组展开为多行。类似于hive中的explode炸裂函数的功能!

-- 创建表
create table t_array_join(
    id UInt8,
    name Array(String)
)engine=Memory();
-- 插入数据
insert into t_array_join values (1,['zs','ls','ww','zl']);
insert into t_array_join values (2,['ny','yf','mq','zq']);
-- 查询数据 array join 炸裂数组
select * from t_array_join;
select id,names
from t_array_join
array join name as names;
-- 查询的数据
1,zs
1,ls
1,ww
1,zl
2,ny
2,yf
2,mq
2,zq

关联查询

join
left join
right join
连接精度
all  所有的 不写默认是
any  任意一个 连接到任意一个
asof  额外的条件

with模型 做项目会用

根据聚合维度  提前计算好所有的组合可能性进行聚合
a  b  c
1  1  1
0  0  0
2^n组合可能性
-- with cube  所有可能
-- with rollup  不能跳过中间的字段
-- with totals  全有 或者 全没有

-- 创建表
drop table if exists tb_with ;
create table tb_with(
	id UInt8 ,
	vist UInt8,
    province String ,
    city String ,
    area String
)engine=MergeTree() 
order by id ;
-- 插入数据
insert into tb_with values(1,12,'山东','济南','历下') ;
insert into tb_with values(2,12,'山东','济南','历下') ;
insert into tb_with values(3,12,'山东','济南','天桥') ;
insert into tb_with values(4,12,'山东','济南','天桥') ;
insert into tb_with values(5,88,'山东','青岛','黄岛') ;
insert into tb_with values(6,88,'山东','青岛','黄岛') ;
insert into tb_with values(7,12,'山西','太原','小店') ;
insert into tb_with values(8,12,'山西','太原','小店') ;
insert into tb_with values(9,112,'山西','太原','尖草坪') ;
-- with cube 
SELECT
province ,
city ,
area
FROM 
tb_with
group by province ,city ,area 
with CUBE ;
-- 查询结果
province|city|area|
--------+----+----+
山东      |青岛  |黄岛  |
山东      |济南  |天桥  |
山西      |太原  |尖草坪 
山东      |济南  |历下  |
山西      |太原  |小店  |
山东      |青岛  |    |
山东      |济南  |    |
山西      |太原  |    |
山东      |    |历下  |
山东      |    |天桥  |
山西      |    |尖草坪 |
山西      |    |小店  |
山东      |    |黄岛  |
山西      |    |    |
山东      |    |    |
        |济南  |历下  |
        |济南  |天桥  |
        |太原  |尖草坪 |
        |青岛  |黄岛  |
        |太原  |小店  |
        |青岛  |    |
        |济南  |    |
        |太原  |    |
        |    |天桥  |
        |    |小店  |
        |    |黄岛  |
        |    |历下  |
        |    |尖草坪 |
        |    |    |
-- with rollup
SELECT 
province ,city ,area
FROM 
tb_with
group by province , city , area 
with rollup;
-- 查询结果
province|city|area|
--------+----+----+
山东      |青岛  |黄岛  |
山东      |济南  |天桥  |
山西      |太原  |尖草坪 |
山东      |济南  |历下  |
山西      |太原  |小店  |
山东      |青岛  |    |
山东      |济南  |    |
山西      |太原  |    |
山西      |    |    |
山东      |    |    |
        |    |    |
-- with totals
SELECT 
province ,city ,area
FROM 
tb_with
group by province , city , area 
with totals;
province|city|area|
--------+----+----+
山东      |青岛  |黄岛  |
山东      |济南  |天桥  |
山西      |太原  |尖草坪 |
山东      |济南  |历下  |
山西      |太原  |小店  |
        |    |    |

函数

八.分布式

配置远程连接

配置文件:  vi /etc/clickhouse-server/config.xml 
1)开启远程连接 放开注释或者复制一个
<listen_host>::</listen_host>

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FERa0rgA-1633685336897)(H:\Snipaste_2021-09-29_21-44-08.png)]

配置zookeeper

配置文件: vi /etc/clickhouse-server/config.xml
<zookeeper>    
 <node index="1">
 <host>doit01</host>
 <port>2181</port>
 </node>
  <node index="2">
 <host>doit02</host>
 <port>2181</port>
 </node>
  <node index="3">
 <host>doit03</host>
 <port>2181</port>
 </node>
 </zookeeper>

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tyVJsr7M-1633685336898)(H:\zookeeper.png)]

在启动之前 先启动zookeeper服务

分发配置文件 scp config.xml doit02$PWD
sudo /etc/init.d/clickhouse-server restart  重启ck服务

创建副本表 分片为1片

-- doit01 
create table tb_demo1 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo1', 'doit01') 
order by id ;

-- doit02 
create table tb_demo1 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo1', 'doit02') 
order by id ;

-- doit03
create table tb_demo1 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo1', 'doit03') 
order by id ;

创建副本表 两个分片

-- doit01 
create table tb_demo2 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'doit01') 
order by id ;

-- doit02 
create table tb_demo2 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'doit02') 
order by id ;

-- doit03
create table tb_demo2 (
id Int8 ,
name String)
engine=ReplicatedMergeTree('/clickhouse/tables/02/tb_demo2', 'doit03') 
order by id ;

分布式创建表

配置集群

在conf.xml 搜索 : /<remote_servers>

<remote_servers>

    <!-- 复制下面的 -->
<cluster01> 
     <!-- 集群中所有的表一个片 -->
	 <shard>
	    <!-- 每个表三个副本 -->
		 <replica>
			 <host>doit01</host>
			 <port>9000</port>
		 </replica>
		  <replica>
			 <host>doit02</host>
			 <port>9000</port>
		 </replica>
		  <replica>
			 <host>doit03</host>
			 <port>9000</port>
		 </replica>
	 </shard>
 </cluster01>

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jvp91qnK-1633685336899)(H:\配置集群.png)]

分发配置文件重启ck服务

创建分布式表

-- 在任何一台服务器上运行SQL语句  就会在01 02 03 机器上创建一个一模一样的表
 -- 1)分布式创建本地表
create table  tb_demo00 on cluster cluster01(
id Int8 ,
name String 
) engine = MergeTree()
order by id ;

-- 2)创建分布式表指向分布式本地表
-- Distributed() 分布式引擎 参数一:指定集群 参数二:默认的default 参数三:指向的本地表 参数四:分片字段
create table demo00_all 
on cluster cluster01 
engine=Distributed('cluster01','default','tb_demo00',id) as tb_demo00 ;
-- 插入数据  分布式表会将插入的数据同步到 01 02 03 上的本地表
insert into demo00_all values(1,'lny');
insert into demo00_all values(2,'yg');

create table tb_demo2 (
id Int8 ,
name String)
engine=ReplicatedMergeTree(’/clickhouse/tables/02/tb_demo2’, ‘doit03’)
order by id ;

##### **分布式创建表**

**配置集群**

在conf.xml  搜索  :  /<remote_servers>

```xml
<remote_servers>

    <!-- 复制下面的 -->
<cluster01> 
     <!-- 集群中所有的表一个片 -->
	 <shard>
	    <!-- 每个表三个副本 -->
		 <replica>
			 <host>doit01</host>
			 <port>9000</port>
		 </replica>
		  <replica>
			 <host>doit02</host>
			 <port>9000</port>
		 </replica>
		  <replica>
			 <host>doit03</host>
			 <port>9000</port>
		 </replica>
	 </shard>
 </cluster01>

[外链图片转存中…(img-Jvp91qnK-1633685336899)]

分发配置文件重启ck服务

创建分布式表

-- 在任何一台服务器上运行SQL语句  就会在01 02 03 机器上创建一个一模一样的表
 -- 1)分布式创建本地表
create table  tb_demo00 on cluster cluster01(
id Int8 ,
name String 
) engine = MergeTree()
order by id ;

-- 2)创建分布式表指向分布式本地表
-- Distributed() 分布式引擎 参数一:指定集群 参数二:默认的default 参数三:指向的本地表 参数四:分片字段
create table demo00_all 
on cluster cluster01 
engine=Distributed('cluster01','default','tb_demo00',id) as tb_demo00 ;
-- 插入数据  分布式表会将插入的数据同步到 01 02 03 上的本地表
insert into demo00_all values(1,'lny');
insert into demo00_all values(2,'yg');

函数

用户行为分析函数 SequenceMatch()

//	SELECT sequenceMatch('(?1)(?2)')(eventTime, eventid = 'event1', eventid = 'event2') FROM funnel_test ;
//		(?N) — 在位置N匹配条件参数。
//		.* — 匹配任何事件的数字。 不需要条件参数来匹配这个模式。
	//	 (?1)(?t>1800)(?2) 匹配彼此发生超过1800秒的事件。
//			可以使用 >=, >, <, <=, == 运算符
//	数据
CREATE TABLE funnel_test 
( 	
	uid String, 
	eventid String, 
	eventTime UInt64
) 
ENGINE = MergeTree 
PARTITION BY (uid, eventTime) 
ORDER BY (uid, eventTime) 
SETTINGS index_granularity = 8192 ;
insert into funnel_test values
('uid1','event1',1551398404) ,
('uid1','event2',1551398406) ,
('uid1','event3',1551398408) ,
('uid2','event2',1551398412) ,
('uid2','event4',1551398415) ,
('uid3','event3',1551398410) ,
('uid3','event4',1551398413) ;
('uid4','event2',1551398410) ,
('uid4','event4',1551398413) ;
 
SELECT uid ,sequenceMatch('(?1)(?2)')(eventTime, eventid = 'event1', eventid = 'event2') FROM funnel_test group by uid;
//	返回的数据,是成功1 失败0
┌─uid──┬─sequenceMatch('(?1)(?2)')(eventTime, equals(eventid, 'event1'), equals(eventid, 'event2'))─┐
│ uid3 │                                                                                          0 │
│ uid1 │                                                                                          1 │
│ uid4 │                                                                                          0 │
│ uid2 │                                                                                          0 │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────┘

漏斗函数windowFunnel()

//返回的数据是满足几个条件
//	数据同上
SELECT uid,windowFunnel(4)(toDateTime(eventTime),eventid = 'event2',eventid = 'event3') as funnel from funnel_test group by uid;
	//	event2到event3的时间不超过4秒
 ┌─uid──┬─funnel─┐
│ uid3 │      0 │
│ uid1 │      2 │
│ uid4 │      1 │
│ uid2 │      2 │