Clickhouse学习笔记03——常用语法及分布式搭建

最新推荐文章于 2024-06-21 16:49:30 发布
最新推荐文章于 2024-06-21 16:49:30 发布
阅读量337 收藏 2
点赞数
分类专栏： Clickhouse 文章标签： Clickhouse
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mp9105/article/details/118851456
版权

文章目录

五、查询语法

1.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,3) ;
1.1 定义变量
WITH
    1 AS start,
    10 AS end
SELECT
    id + start,
    *
FROM tb_mysql
 
┌─plus(id, start)─┬─id─┬─name─┬─age─┐
│               2 │  1 │ zss  │  23 │
│               3 │  2 │ lss  │  33 │
│               4 │  3 │ ww   │  44 │
│               2 │  1 │ zss  │  23 │
│               3 │  2 │ lss  │  33 │
│               2 │  1 │ zss  │  23 │
│               3 │  2 │ lss  │  33 │
└─────────────────┴────┴──────┴─────┘
1.2 调用函数
SELECT *
FROM tb_partition
┌─id─┬─name─┬────────────birthday─┐
│  1 │ xl   │ 2021-05-20 10:50:46 │
│  2 │ xy   │ 2021-05-20 11:17:47 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────birthday─┐
│  3 │ xf   │ 2021-05-19 11:11:12 │
└────┴──────┴───────────---------─┘
WITH toDate(birthday) AS bday
SELECT
    id,
    name,
    bday
FROM tb_partition
 
┌─id─┬─name─┬───────bday─┐
│  1 │ xl   │ 2021-05-20 │
│  2 │ xy   │ 2021-05-20 │
└────┴──────┴────────────┘
┌─id─┬─name─┬───────bday─┐
│  3 │ xf   │ 2021-05-19 │
└────┴──────┴────────────┘
1.3 子查询

可以定义子查询 ,但是一定还要注意的是,子查询只能返回一行结果 ,否则会跑出异常

WITH
    (
        SELECT *
        FROM tb_partition
        WHERE id = 1
    ) AS sub
SELECT
    *,
    sub
FROM tb_partition
 
┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐
│  1 │ xl   │ 2021-05-20 10:50:46 │ (1,'xl','2021-05-20 10:50:46') │
│  2 │ xy   │ 2021-05-20 11:17:47 │ (1,'xl','2021-05-20 10:50:46') │
└────┴──────┴─────────────────────┴────────────────────────────────┘
┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐
│  3 │ xf   │ 2021-05-19 11:11:12 │ (1,'xl','2021-05-20 10:50:46') │
└────┴──────┴─────────────────────┴────────────────────────────────┘

2.from

SQL是一种面向集合的编程语言 ,from决定了程序从那里读取数据

  1. 表中查询数据

  2. 子查询中查询数据

  3. 表函数中查询数据 select * from numbers(3) ;

3.表函数

构建表的函数 , 使用场景如下:

SELECT查询的[FROM)子句。

创建表AS 查询。

image-20210520213459584

file

file(path, format, structure)
path — The relative path to the file from user_files_path. Path to file support following globs in readonly mode: *, ?, {abc,def} and {N..M} where N, M — numbers, `'abc', 'def' — strings.
format — The format of the file.
structure — Structure of the table. Format 'column1_name column1_type, column2_name column2_type, ...'.

数据文件必须在指定的目录下 /var/lib/clickhouse/user_files

SELECT *
FROM file('demo.csv', 'CSV', 'id Int8,name String , age UInt8')
-- 文件夹下任意的文件
SELECT *
FROM file('*', 'CSV', 'id Int8,name String , age UInt8')

numbers

SELECT *
FROM numbers(10) ;
 
SELECT *
FROM numbers(2, 10) ;
 
SELECT *
FROM numbers(10) limit 3 ;
 
SELECT toDate('2020-01-01') + number AS d
FROM numbers(365)

mysql

-- CK可以直接从mysql服务中查询数据
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);
SELECT *
FROM mysql('linux01:3306', 'db_doit_ch', 'emp', 'root', 'root')

image-20210520215316752

SELECT *FROM hdfs(‘hdfs://hdfs1:9000/test’, ‘TSV’, ‘column1 UInt32, column2 UInt32, column3 UInt32’)LIMIT 2

SELECT *

FROM hdfs(‘hdfs://linux01:8020/demo.csv’, ‘CSV’, ‘id Int8 ,name String , age Int8’)

img

4.array join

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

CREATE TABLE test_arrayjoin
(
  `name` String,
  `vs` Array(Int8)
)
ENGINE = Memory ;
insert into test_arrayjoin values('xw',[1,2,3]),('xl',[4,5]),('xk',[1]);
-- 将数组中的数据展开
SELECT
  *,
  s
FROM test_arrayjoin
ARRAY JOIN vs AS s
┌─name─┬─vs──────┬─s─┐
│ xw   │ [1,2,3] │ 1 │
│ xw   │ [1,2,3] │ 2 │
│ xw   │ [1,2,3] │ 3 │
│ xl   │ [4,5]   │ 4 │
│ xl   │ [4,5]   │ 5 │
│ xk   │ [1]     │ 1 │
└──────┴─────────┴───┘
-- arrayMap 高阶函数,对数组中的每个元素进行操作
SELECT
  *,
  arrayMap(x->x*2 , vs) vs2
FROM test_arrayjoin ;
SELECT
  *,
  arrayMap(x -> (x * 2), vs) AS vs2
FROM test_arrayjoin
┌─name─┬─vs──────┬─vs2─────┐
│ xw   │ [1,2,3] │ [2,4,6] │
│ xl   │ [4,5]   │ [8,10]  │
│ xk   │ [1]     │ [2]     │
└──────┴─────────┴─────────┘
SELECT
  *,
  arrayMap(x -> (x * 2), vs) AS vs2 ,
  vv1 ,
  vv2
FROM test_arrayjoin
array join
vs as vv1 ,
vs2 as vv2 ;
┌─name─┬─vs──────┬─vs2─────┬─vv1─┬─vv2─┐
│ xw   │ [1,2,3] │ [2,4,6] │   1 │   2 │
│ xw   │ [1,2,3] │ [2,4,6] │   2 │   4 │
│ xw   │ [1,2,3] │ [2,4,6] │   3 │   6 │
│ xl   │ [4,5]   │ [8,10]  │   4 │   8 │
│ xl   │ [4,5]   │ [8,10]  │   5 │  10 │
│ xk   │ [1]     │ [2]     │   1 │   2 │
└──────┴─────────┴─────────┴─────┴─────┘
select
id ,
h ,
xx
from
tb_array_join
array join
hobby  as h  ,
arrayEnumerate(hobby) as xx ;
┌─id─┬─h─────┬─xx─┐
│  1 │ eat   │  1 │
│  1 │ drink │  2 │
│  1 │ sleep │  3 │
│  2 │ study │  1 │
│  2 │ sport │  2 │
│  2 │ read  │  3 │
└────┴───────┴────┘
┌─id─┬─h─────┬─xx─┐
│  3 │ eat   │  1 │
│  3 │ drink │  2 │

案例

a,2017-02-05,200
a,2017-02-06,300
a,2017-02-07,200
a,2017-02-08,400
a,2017-02-08,300
a,2017-02-10,600
b,2017-02-05,200
b,2017-02-06,300
b,2017-02-08,200
b,2017-02-09,400
b,2017-02-10,600
c,2017-01-31,200
c,2017-02-01,300
c,2017-02-02,200
c,2017-02-03,400
c,2017-02-10,600
a,2017-03-01,200
a,2017-03-02,300
a,2017-03-03,200
a,2017-03-04,400
a,2017-03-05,600
DROP TABLE IF EXISTS tb_shop ;
CREATE TABLE tb_shop
(
    `name` String,
    `cdate` Date,
    `cost` Float64
)engine=ReplacingMergeTree(cdate)
order by (name,cdate) ;
-- 导入数据
clickhouse-client -q 'insert into doit23.tb_shop format CSV' < shop.txt ;
┌─name─┬──────cdate─┬─cost─┐
│ a    │ 2017-02-05 │  200 │
│ a    │ 2017-02-06 │  300 │
│ a    │ 2017-02-07 │  200 │
│ a    │ 2017-02-08 │  400 │
│ a    │ 2017-02-10 │  600 │
│ a    │ 2017-03-01 │  200 │
│ a    │ 2017-03-02 │  300 │
│ a    │ 2017-03-03 │  200 │
│ a    │ 2017-03-04 │  400 │
│ a    │ 2017-03-05 │  888 │
│ b    │ 2017-02-05 │  200 │
│ b    │ 2017-02-06 │  300 │
│ b    │ 2017-02-08 │  200 │
│ b    │ 2017-02-09 │  400 │
│ b    │ 2017-02-10 │  600 │
│ c    │ 2017-01-31 │  200 │
│ c    │ 2017-02-01 │  300 │
│ c    │ 2017-02-02 │  200 │
│ c    │ 2017-02-03 │  400 │
│ c    │ 2017-02-10 │  600 │
└──────┴────────────┴──────┘
select
name ,
groupArray(cdate) arr ,
arrayEnumerate(arr) as indexs
from
tb_shop
group by name;
 
┌─name─┬─arr─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─indexs─────────────────┐
│ b    │ ['2017-02-05','2017-02-06','2017-02-08','2017-02-09','2017-02-10']                                                                  │ [1,2,3,4,5]            │
│ c    │ ['2017-01-31','2017-02-01','2017-02-02','2017-02-03','2017-02-10']                                                                  │ [1,2,3,4,5]            │
│ a    │ ['2017-02-05','2017-02-06','2017-02-07','2017-02-08','2017-02-10','2017-03-01','2017-03-02','2017-03-03','2017-03-04','2017-03-05'] │ [1,2,3,4,5,6,7,8,9,10] │
└──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────┘
select
name ,
dt - num
from
(select
name ,
groupArray(cdate) arr ,
arrayEnumerate(arr) as indexs
from
tb_shop
group by name
)
array  join
arr as dt ,
indexs as num ;
┌─name─┬─minus(dt, num)─┐
│ b    │     2017-02-04 │
│ b    │     2017-02-04 │
│ b    │     2017-02-05 │
│ b    │     2017-02-05 │
│ b    │     2017-02-05 │
│ c    │     2017-01-30 │
│ c    │     2017-01-30 │
│ c    │     2017-01-30 │
│ c    │     2017-01-30 │
│ c    │     2017-02-05 │
│ a    │     2017-02-04 │
│ a    │     2017-02-04 │
│ a    │     2017-02-04 │
│ a    │     2017-02-04 │
│ a    │     2017-02-05 │
│ a    │     2017-02-23 │
│ a    │     2017-02-23 │
│ a    │     2017-02-23 │
│ a    │     2017-02-23 │
│ a    │     2017-02-23 │
└──────┴────────────────┘
select
name ,
diff ,
count(1) cnt
from
(select
name ,
(dt - num) as diff
from
(select
name ,
groupArray(cdate) arr ,
arrayEnumerate(arr) as indexs
from
tb_shop
group by name
)
array  join
arr as dt ,
indexs as num
)
group by name , diff;
 
┌─name─┬───────diff─┬─count(1)─┐
│ b    │ 2017-02-04 │        2 │
│ a    │ 2017-02-23 │        5 │
│ c    │ 2017-01-30 │        4 │
│ c    │ 2017-02-05 │        1 │
│ a    │ 2017-02-04 │        4 │
│ b    │ 2017-02-05 │        3 │
│ a    │ 2017-02-05 │        1 │
└──────┴────────────┴──────────┘
select
name ,
diff ,
count(1) cnt
from
(select
name ,
(dt - num) as diff
from
(select
name ,
groupArray(cdate) arr ,
arrayEnumerate(arr) as indexs
from
tb_shop
group by name
)
array  join
arr as dt ,
indexs as num
)
group by name , diff
order by cnt desc
limit 1 by name  ;
 
┌─name─┬───────diff─┬─cnt─┐
│ a    │ 2017-02-23 │   5 │
│ c    │ 2017-01-30 │   4 │
│ b    │ 2017-02-05 │   3 │
└──────┴────────────┴─────┘

5.关联查询

所有标准 SQL JOIN 支持类型:

  • INNER JOIN, only matching rows are returned.
  • LEFT OUTER JOIN, non-matching rows from left table are returned in addition to matching rows.
  • RIGHT OUTER JOIN, non-matching rows from right table are returned in addition to matching rows.
  • FULL OUTER JOIN, non-matching rows from both tables are returned in addition to matching rows.
  • CROSS JOIN, produces cartesian product of whole tables, “join keys” are not specified.

JOIN子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。

所有标准 SQL JOIN 支持类型:

  • INNER JOIN, only matching rows are returned.
  • LEFT OUTER JOIN, non-matching rows from left table are returned in addition to matching rows.
  • RIGHT OUTER JOIN, non-matching rows from right table are returned in addition to matching rows.
  • FULL OUTER JOIN, non-matching rows from both tables are returned in addition to matching rows.
  • CROSS JOIN, produces cartesian product of whole tables, “join keys” are not specified.

JOIN子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。

img

连接精度

连接精度决定了JOIN查询在连接数据时所使用的策略，目前支持ALL、ANY和ASOF三种类型。如果不主动声明，则默认是ALL。可以通过join_default_strictness配置参数修改默认的连接精度类型。

对数据是否连接匹配的判断是通过JOIN KEY进行的，目前只支持等式（EQUAL JOIN）。交叉连接（CROSS JOIN）不需要使用JOIN KEY，因为它会产生笛卡儿积。

-- 准备数据
drop table if exists yg ;
create table yg(
id Int8 ,
name String ,
age UInt8  ,
bid Int8
)engine=Log ;
insert into  yg values(1,'AA',23,1) ,
(2,'BB',24,2) ,
(3,'VV',27,1) ,
(4,'CC',13,3) ,
(5,'KK',53,3) ,
(6,'MM',33,3)  ;
 
drop table if exists bm ;
create table bm(
bid Int8 ,
name String
)engine=Log ;
insert into bm values(1,'x'),(2,'Y'),(3,'Z');
 
drop table if exists gz ;
drop table gz ;
create table gz(
id Int8 ,
jb Int64 ,
jj Int64
)engine=Log ;
insert into gz values (1,1000,2000),(1,1000,2000),(2,2000,1233),(3,2000,3000),(4,4000,1000),(5,5000,2000);
4.1 all

如果左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据。而判断连接匹配的依据是左表与右表内的数据，基于连接键（JOIN KEY）的取值完全相等（equal），等同于 left.key=right.key。

SELECT *
FROM yg AS inser
ALL INNER JOIN gz ON yg.id = gz.id ;
SELECT *
FROM yg AS inser
ALL  JOIN gz ON yg.id = gz.id ;
SELECT *
FROM yg AS inser
JOIN gz ON yg.id = gz.id ;
 
┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐
│  1 │ AA   │  23 │   1 │     1 │ 1000 │ 2000 │
│  1 │ AA   │  23 │   1 │     1 │ 1000 │ 2000 │
│  2 │ BB   │  24 │   2 │     2 │ 2000 │ 1233 │
│  3 │ VV   │  27 │   1 │     3 │ 2000 │ 3000 │
│  4 │ CC   │  13 │   3 │     4 │ 4000 │ 1000 │
│  5 │ KK   │  53 │   3 │     5 │ 5000 │ 2000 │
└────┴──────┴─────┴─────┴───────┴──────┴──────┘
4.2 any

如果左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据。ANY与ALL判断连接匹配的依据相同。

SELECT *
FROM yg
ANY INNER JOIN gz ON yg.id = gz.id
 
┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐
│  1 │ AA   │  23 │   1 │     1 │ 1000 │ 2000 │
│  2 │ BB   │  24 │   2 │     2 │ 2000 │ 1233 │
│  3 │ VV   │  27 │   1 │     3 │ 2000 │ 3000 │
│  4 │ CC   │  13 │   3 │     4 │ 4000 │ 1000 │
│  5 │ KK   │  53 │   3 │     5 │ 5000 │ 2000 │
└────┴──────┴─────┴─────┴───────┴──────┴──────┘
4.3 asof

asof连接键之后追加定义一个模糊连接的匹配条件asof_column。

drop table if exists emp1 ;
create table emp1(
id Int8 ,
name String ,
ctime DateTime
)engine=Log ;
insert into emp1 values(1,'AA','2021-01-03 00:00:00'),
(1,'AA','2021-01-02 00:00:00'),
(2,'CC','2021-01-01 00:00:00'),
(3,'DD','2021-01-01 00:00:00'),
(4,'EE','2021-01-01 00:00:00');
 
drop table if exists emp2 ;
create table emp2(
id Int8 ,
name String ,
ctime DateTime
)engine=Log ;
insert into emp2 values(1,'aa','2021-01-02 00:00:00'),
(1,'aa','2021-01-02 00:00:00'),
(2,'cc','2021-01-01 00:00:00'),
(3,'dd','2021-01-01 00:00:00');
 
-- ASOF inner join
SELECT *
FROM emp2
ASOF INNER JOIN emp1 ON (emp1.id = emp2.id) AND (emp1.ctime > emp2.ctime)
 
┌─id─┬─name─┬───────────────ctime─┬─emp1.id─┬─emp1.name─┬──────────emp1.ctime─┐
│  1 │ aa   │ 2021-01-02 00:00:00 │       1 │ AA        │ 2021-01-03 00:00:00 │
│  1 │ aa   │ 2021-01-02 00:00:00 │       1 │ AA        │ 2021-01-03 00:00:00 │
└────┴──────┴─────────────────────┴─────────┴───────────┴─────────────────────┘

6.with模型

  • With cube
  • With rollup
  • With totals
drop table is 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,'山西','太原','尖草坪') ;
SELECT
    province,
    city,
    area,
    sum(vist)
FROM tb_with
GROUP BY
    province,
    city,
    area
    WITH CUBE ;
  ┌─province─┬─city─┬─area───┬─sum(vist)─┐
│ 山东     │ 青岛 │ 黄岛   │       176 │
│ 山东     │ 济南 │ 天桥   │        24 │
│ 山东     │ 太原 │ 尖草坪 │       112 │
│ 山东     │ 济南 │ 历下   │        24 │
│ 山西     │ 太原 │ 小店   │        12 │
│ 山东     │ 太原 │ 小店   │        12 │
└──────────┴──────┴────────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│ 山东     │ 青岛 │      │       176 │
│ 山东     │ 济南 │      │        48 │
│ 山西     │ 太原 │      │        12 │
│ 山东     │ 太原 │      │       124 │
└──────────┴──────┴──────┴───────────┘
┌─province─┬─city─┬─area───┬─sum(vist)─┐
│ 山东     │      │ 历下   │        24 │
│ 山东     │      │ 小店   │        12 │
│ 山东     │      │ 天桥   │        24 │
│ 山西     │      │ 小店   │        12 │
│ 山东     │      │ 尖草坪 │       112 │
│ 山东     │      │ 黄岛   │       176 │
└──────────┴──────┴────────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│ 山西     │      │      │        12 │
│ 山东     │      │      │       348 │
└──────────┴──────┴──────┴───────────┘
┌─province─┬─city─┬─area───┬─sum(vist)─┐
│          │ 济南 │ 历下   │        24 │
│          │ 济南 │ 天桥   │        24 │
│          │ 太原 │ 尖草坪 │       112 │
│          │ 青岛 │ 黄岛   │       176 │
│          │ 太原 │ 小店   │        24 │
└──────────┴──────┴────────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│          │ 青岛 │      │       176 │
│          │ 济南 │      │        48 │
│          │ 太原 │      │       136 │
└──────────┴──────┴──────┴───────────┘
┌─province─┬─city─┬─area───┬─sum(vist)─┐
│          │      │ 天桥   │        24 │
│          │      │ 小店   │        24 │
│          │      │ 黄岛   │       176 │
│          │      │ 历下   │        24 │
│          │      │ 尖草坪 │       112 │
└──────────┴──────┴────────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│          │      │      │       360 │
└──────────┴──────┴──────┴───────────┘
SELECT
    province,
    city,
    area,
    sum(vist)
FROM tb_with
GROUP BY
    province,
    city,
    area
    WITH ROLLUP;
 
┌─province─┬─city─┬─area───┬─sum(vist)─┐
│ 山东     │ 青岛 │ 黄岛   │       176 │
│ 山东     │ 济南 │ 天桥   │        24 │
│ 山东     │ 太原 │ 尖草坪 │       112 │
│ 山东     │ 济南 │ 历下   │        24 │
│ 山西     │ 太原 │ 小店   │        12 │
│ 山东     │ 太原 │ 小店   │        12 │
└──────────┴──────┴────────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│ 山东     │ 青岛 │      │       176 │
│ 山东     │ 济南 │      │        48 │
│ 山西     │ 太原 │      │        12 │
│ 山东     │ 太原 │      │       124 │
└──────────┴──────┴──────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│ 山西     │      │      │        12 │
│ 山东     │      │      │       348 │
└──────────┴──────┴──────┴───────────┘
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│          │      │      │       360 │
└──────────┴──────┴──────┴───────────┘
SELECT
    province,
    city,
    area,
    sum(vist)
FROM tb_with
GROUP BY
    province,
    city,
    area
    WITH TOTALS;
┌─province─┬─city─┬─area───┬─sum(vist)─┐
│ 山东     │ 青岛 │ 黄岛   │       176 │
│ 山东     │ 济南 │ 天桥   │        24 │
│ 山东     │ 太原 │ 尖草坪 │       112 │
│ 山东     │ 济南 │ 历下   │        24 │
│ 山西     │ 太原 │ 小店   │        12 │
│ 山东     │ 太原 │ 小店   │        12 │
└──────────┴──────┴────────┴───────────┘
Totals:
┌─province─┬─city─┬─area─┬─sum(vist)─┐
│          │      │      │       360 │
└──────────┴──────┴──────┴───────────┘

六、 函数

ClickHouse主要提供两类函数—普通函数和聚合函数。普通函数由IFunction接口定义，拥有数十种函数实现，例如FunctionFormatDateTime、FunctionSubstring等。除了一些常见的函数 ( 诸如四则运算、日期转换等 ) 之外，也不乏一些非常实用的函数，例如网址提取函数、IP地址脱敏函数等。普通函数是没有状态的，函数效果作用于每行数据之上。当然，在函数具体执行的过程中，并不会一行一行地运算，而是采用向量化的方式直接作用于一整列数据。聚合函数由IAggregateFunction接口定义，相比无状态的普通函数，聚合函数是有状态的。以COUNT聚合函数为例，其AggregateFunctionCount的状态使用整UInt64记录。聚合函数的状态支持序列化与反序列化，所以能够在分布式节点之间进行传输，以实现增量计算。

1 类型转换函数

  • toInt8(expr) — Results in the Int8 data type.
  • toInt16(expr) — Results in the Int16 data type.
  • toInt32(expr) — Results in the Int32 data type.
  • toInt64(expr) — Results in the Int64 data type.
  • toInt128(expr) — Results in the Int128 data type.
  • toInt256(expr) — Results in the Int256 data type.
SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);
┌─────────toInt64(nan)─┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│ -9223372036854775808 │          32 │            16 │           8 │
└──────────────────────┴─────────────┴───────────────┴─────────────┘

  • toUInt(8|16|32|64|256)OrZero

  • toUInt(8|16|32|64|256)OrNull

  • toFloat(32|64)

  • toFloat(32|64)OrZero

  • toFloat(32|64)OrNull

  • toDate

  • toDateOrZero

  • toDateOrNull

  • toDateTime

  • toDateTimeOrZero

  • toDateTimeOrNull

  • toDecimal(32|64|128|256)

toString

    now() AS now_local,
    toString(now(), 'Asia/Yekaterinburg') AS now_yekat;
    ┌───────────now_local─┬─now_yekat───────────┐
    │ 2016-06-15 00:11:21 │ 2016-06-15 02:11:21 │
    └─────────────────────┴─────────────────────┘
  • CAST(x, T)
Arguments
- `x` — Any type.
- `T` — Destination type. String
**Returned value**

SELECT
    '2016-06-15 23:00:00' AS timestamp,
    CAST(timestamp AS DateTime) AS datetime,
    CAST(timestamp AS Date) AS date,
    CAST(timestamp, 'String') AS string,
    CAST(timestamp, 'FixedString(22)') AS fixed_string;
    ┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────

2 日期函数

SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date_local,
    toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
    toString(time, 'US/Samoa') AS time_samoa
┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘
  • toDate
  • toYear
  • toMonth
  • toHour
  • toMinute
  • toSecond
  • toUnixTimestamp
  • date_trunc 将时间截断 date_trunc(unit, value[, timezone])
second
minute
hour
day
week
month
quarter
year
 
SELECT now(), date_trunc('hour', now());
┌───────────────now()─┬─date_trunc('hour', now())─┐
│ 2021-05-21 13:52:42 │       2021-05-21 13:00:00 │
└─────────────────────┴───────────────────────────┘
  • date_add
date_add(unit, value, date)
second
minute
hour
day
week
month
quarter
year
SELECT date_add(YEAR, 3, toDate('2018-01-01'));
date_diff('unit', startdate, enddate, [timezone])
  • date_diff
  • date_sub
  • timestamp_add
  • timestamp_sub
  • toYYYYMM
  • toYYYYMMDD
  • toYYYYMMDDhhmmss
  • formatDateTime
%Cyear divided by 100 and truncated to integer (00-99)20
%dday of the month, zero-padded (01-31)02
%DShort MM/DD/YY date, equivalent to %m/%d/%y01/02/18
%eday of the month, space-padded ( 1-31)2
%Fshort YYYY-MM-DD date, equivalent to %Y-%m-%d2018-01-02
%Gfour-digit year format for ISO week number, calculated from the week-based year [defined by the ISO 860 standard, normally useful only with %V2018
%gtwo-digit year format, aligned to ISO 8601, abbreviated from four-digit notation18
%Hhour in 24h format (00-23)22
%Ihour in 12h format (01-12)10
%jday of the year (001-366)002
%mmonth as a decimal number (01-12)01
%Mminute (00-59)33
%nnew-line character (‘’)
%pAM or PM designationPM
%QQuarter (1-4)1
%R24-hour HH:MM time, equivalent to %H:%M22:33
%Ssecond (00-59)44
%thorizontal-tab character (’)
%TISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S22:33:44
%uISO 8601 weekday as number with Monday as 1 (1-7)2
%VISO 8601 week number (01-53)01
%wweekday as a decimal number with Sunday as 0 (0-6)2
%yYear, last two digits (00-99)18
%YYear2018
%%a % sign%
SELECT formatDateTime(now(), '%D')
 
┌─formatDateTime(now(), '%D')─┐
│ 05/21/21                    │
└─────────────────────────────┘

  • FROM_UNIXTIME

    SELECT FROM_UNIXTIME(423543535)
 
┌─FROM_UNIXTIME(423543535)─┐
│      1983-06-04 10:58:55 │
└──────────────────────────┘

3 条件函数

  • if(exp1 , exp2,exp3)
  • multiIf()
drop table if exists tb_if;
create table if not exists tb_if(
    uid Int16,
    name String ,
    gender String
)engine = TinyLog ;
insert into tb_if values(1,'zss1','M') ;
insert into tb_if values(2,'zss2','M') ;
insert into tb_if values(3,'zss3','F') ;
insert into tb_if values(4,'zss4','O') ;
insert into tb_if values(5,'zss5','F') ;
--------单条件判断---------
SELECT
    *,
    if(gender = 'M', '男', '女')
FROM tb_if
 
┌─uid─┬─name─┬─gender─┬─if(equals(gender, 'M'), '男', '女')─┐
│   1 │ zss1 │ M      │ 男                                  │
│   2 │ zss2 │ M      │ 男                                  │
│   3 │ zss3 │ F      │ 女                                  │
│   4 │ zss4 │ O      │ 女                                  │
│   5 │ zss5 │ F      │ 女                                  │
-------------------------------------------------------------
多条件判断
 
SELECT
    *,
    multiIf(gender = 'M', '男', gender = 'F', '女', '保密') AS sex
FROM tb_if
 
┌─uid─┬─name─┬─gender─┬─sex──┐
│   1 │ zss1 │ M      │ 男   │
│   2 │ zss2 │ M      │ 男   │
│   3 │ zss3 │ F      │ 女   │
│   4 │ zss4 │ O      │ 保密 │
│   5 │ zss5 │ F      │ 女   │
└────┴─────┴───────┴─────┘

4 其他

visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc') = '"\\n\\u0000"';
visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc') = '{"def":[1,2,3]}';
select  JSONExtract('{"a":"hello","b":"tom","c":12}', 'Tuple(String,String,UInt8)') as kn;
-- 元组函数
select tupleElement((1,2,3,4,66),5);
 
-- BitMap 函数
-- bitmapBuild
SELECT
    bitmapBuild([1, 2, 3, 4, 5]) AS res,
    toTypeName(res)
┌─res─┬─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─┐
│     │ AggregateFunction(groupBitmap, UInt8)    │
└─────┴──────────────────────────────────────────┘
-- bitmapToArray
SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
-- bitmapSubsetInRange
SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0, 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, 27, 28, 29, 30, 31, 32, 33, 100, 200, 500]), toUInt32(30), toUInt32(200))) AS res
┌─res───────────────┐
│ [30,31,32,33,100] │
└───────────────────┘
-- bitmapContains
SELECT bitmapContains(bitmapBuild([1, 5, 7, 9]), toUInt32(9)) AS res
 
┌─res─┐
│   1 │
└─────┘
-- bitmapHasAny 有任意一个元素
SELECT bitmapHasAny(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5])) AS res
 
┌─res─┐
│   1 │
└─────
-- bitmapHasAll 有任意一个元素
 
-- bitmapMin
-- bitmapMax
-- bitmapAnd 交集
-- bitmapOr 并集
-- bitmapAndnot差集

七 、分布式

1.集群概念

集群是副本和分片的基础，它将ClickHouse的服务拓扑由单节点延 伸到多个节点，但它并不像Hadoop生态的某些系统那样，要求所有节点组成一个单一的大集群。ClickHouse的集群配置非常灵活，用户既可以将所有节点组成一个单一集群，也可以按照业务的诉求，把节点划分为多个小的集群。在每个小的集群区域之间，它们的节点、分区和副本数量可以各不相同

img

另一种是从功能作用层面区分，使用副本的主要目的是防止数据丢失，增加数据存储的冗余；而使用分片的主要目的是实现数据的水平切分，

经讲过MergerTree的命名规则。如果在*MergeTree的前面增加Replicated的前缀，则能够组合 成一个新的变种引擎，即Replicated-MergeTree复制表!

20210603230438.png

只有使用了ReplicatedMergeTree复制表系列引擎，才能应用副本的能力（后面会介绍另一种副本的实现方式）。或者用一种更为直接的方式理解，即使用ReplicatedMergeTree的数据表就是副本。 ReplicatedMergeTree是MergeTree的派生引擎，它在MergeTree的 基础上加入了分布式协同的能力，

image-20210521213553543

在MergeTree中，一个数据分区由开始创建到全部完成，会历经两类存储区域。

  • 内存：数据首先会被写入内存缓冲区。

  • 本地磁盘：数据接着会被写入tmp临时目录分区，待全部完成后再将临时目录重命名为正式分区。

ReplicatedMergeTree在上述基础之上增加了ZooKeeper的部分，它会进一步在ZooKeeper内创建一系列的监听节点，并以此实现多个实例之间的通信。在整个通信过程中，ZooKeeper并不会涉及表数据的传输。

  • 依赖ZooKeeper：在执行INSERT和ALTER查询的时候，ReplicatedMergeTree需要借助ZooKeeper的分布式协同能力，以实现多个副本之间的同步。但是在查询副本的时候，并不需要使用 ZooKeeper。关于这方面的更多信息，会在稍后详细介绍。
  • 表级别的副本：副本是在表级别定义的，所以每张表的副本配置都可以按照它的实际需求进行个性化定义，包括副本的数量，以及副本在集群内的分布位置等。
  • 多主架构（Multi Master）：可以在任意一个副本上执行INSERT和ALTER查询，它们的效果是相同的。这些操作会借助ZooKeeper的协同能力被分发至每个副本以本地形式执行。
  • Block数据块：在执行INSERT命令写入数据时，会依据 max_insert_block_size的大小（默认1048576行）将数据切分成若干个Block数据块。所以Block数据块是数据写入的基本单元，并且具有 写入的原子性和唯一性。
  • 原子性：在数据写入时，一个Block块内的数据要么全部写入成功，要么全部失败。
  • 唯一性：在写入一个Block数据块的时候，会按照当前Block数据块的数据顺序、数据行和数据大小等指标，计算Hash信息摘要并记录在案。在此之后，如果某个待写入的Block数据块与先前已被写入的 Block数据块拥有相同的Hash摘要（Block数据块内数据顺序、数据大小和数据行均相同），则该Block数据块会被忽略。这项设计可以预防由异常原因引起的Block数据块重复写入的问题。

2.分片概念

通过引入数据副本，虽然能够有效降低数据的丢失风险（多份存储），并提升查询的性能（分摊查询、读写分离），但是仍然有一个问题没有解决，那就是数据表的容量问题。到目前为止，每个副本自

身，仍然保存了数据表的全量数据。所以在业务量十分庞大的场景中，依靠副本并不能解决单表的性能瓶颈。想要从根本上解决这类问题，需要借助另外一种手段，即进一步将数据水平切分，也就是我们将要介绍的数据分片。ClickHouse中的每个服务节点都可称为一个shard（分片）。从理论上来讲，假设有N(N>=1)张数据表A，分布在N个ClickHouse服务节点，而这些数据表彼此之间没有重复数据，那么就可以说数据表A拥有N个分片。然而在工程实践中，如果只有这些分片表，那么整个 Sharding（分片）方案基本是不可用的。对于一个完整的方案来说，还需要考虑数据在写入时，如何被均匀地写至各个shard，以及数据在查询时，如何路由到每个shard，并组合成结果集。所以，ClickHouse

的数据分片需要结合Distributed表引擎一同使用

img

Distributed表引擎自身不存储任何数据，它能够作为分布式表的一层透明代理，在集群内部自动开展数据的写入、分发、查询、路由等工作

3.配置zookeeper

需要在每台CK的节点上配置ZK的位置

ClickHouse使用一组zookeeper标签定义相关配置，默认情况下，在全局配置config.xml中定义即可。但是各个副本所使用的Zookeeper 配置通常是相同的，为了便于在多个节点之间复制配置文件，更常见的做法是将这一部分配置抽离出来，独立使用一个文件保存。

首先，在服务器的/etc/clickhouse-server/config.d目录下创建一个名为metrika.xml的配置文件：

<?xml version="1.0"?>
<yandex>
    <zookeeper-servers>
        <node index="1">
            <host>linux201</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>linux202</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>linux203</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>
</yandex>

接着，在全局配置config.xml中的大约第9行，使用<include_from>标签导入刚才定义的配置

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

image-20210613082606072

403行左右引用ZK的地址

<zookeeper incl="zookeeper-servers" optional="false" />

image-20210613083728273

Note：incl与metrika.xml配置文件内的节点名称要彼此对应——本配置中均为zookeeper-servers

ClickHouse在它的系统表中，颇为贴心地提供了一张名为zookeeper的代理表。通过这张表，可以使用SQL查询的方式读取远端ZooKeeper内的数据。有一点需要注意，在用于查询的SQL语句中，必须指定path条件，

将配置文件同步到其他集群节点!!

scp -r config.d/  linux202:$PWD
scp -r config.d/  linux203:$PWD
 
scp config.xml   linux202:$PWD
scp config.xml   linux203:$PWD

4.创建副本表

在创建副本表以前，首先要启动集群中的zookeeper

首先，由于增加了数据的冗余存储，所以降低了数据丢失的风险；其次，由于副本采用了多主架构，所以每个副本实例都可以作为数据读、写的入口，这无疑分摊了节点的负载。

在只使用副本功能的时候 , 我们对CK集群不需要任何的配置就可以实现数据的多副本存储！只需要在建表的时候指定engine和ZK的位置即可 ！

ENGINE = ReplicatedMergeTree('zk_path', 'replica_name')
 
# /clickhouse/tables/{shard}/table_name
# /clickhouse/tables/ 是约定俗成的路径固定前缀，表示存放数据表的根路径。
# 在'zk_path'相同而'replica_name'不同时，表示clickhouse通过zk管理的同一张表的不同副本

  • {shard} 表示分片编号，通常用数值替代，例如01、02、03。一张数据表可以有多个分片，而每个分片都拥有自己的副本。

  • table_name 表示数据表的名称，为了方便维护，通常与物理表的名字相同（虽然ClickHouse并不强制要求路径中的表名称和物理表名相同），可以使用多级目录描述库名与表名；

  • replica_name 作用是定义在ZooKeeper中创建的副本名称，该名称是区分不同副本实例的唯一标识。一种约定成俗的命名方式是使用所在服务器的域名称。

  • 对于zk_path而言，同一张数据表的同一个分片的不同副本，应该定义相同的路径；而对于replica_name而言，同一张数据表的同一个分片的不同副本，应该定义不同的名称

4.1 一个分片

一个分片，多个副本表

-- lixnu201 机器
create table dbtest.tb_demo1 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/dbtest/tb_demo1', 'linux201')
order by id;
-- lixnu202 机器
create table dbtest.tb_demo1 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/dbtest/tb_demo1', 'linux202')
order by id;
-- lixnu203 机器
create table dbtest.tb_demo1 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/dbtest/tb_demo1', 'linux203')
order by id;

查看zookeeper中的内容

[zk: localhost:2181(CONNECTED) 0] ls /
[a, zookeeper, clickhouse, DNS, datanode1, server1, hbase]
[zk: localhost:2181(CONNECTED) 1] ls /clickhouse
[tables, task_queue]
[zk: localhost:2181(CONNECTED) 2] ls /clickhouse/tables
[01]
[zk: localhost:2181(CONNECTED) 3] ls /clickhouse/tables/01
[tb_demo1]
[zk: localhost:2181(CONNECTED) 4] ls /clickhouse/tables/01/tb_demo1
[metadata, temp, mutations, log, leader_election, columns, blocks, nonincrement_block_numbers, replicas, quorum, block_numbers]
[zk: localhost:2181(CONNECTED) 5] ls /clickhouse/tables/01/tb_demo1/replicas
[linux02, linux03, linux01]

SELECT *
FROM system.zookeeper
WHERE path = '/' ;

至此，在任何一台节点上,插入数据，在其他节点上都能同步数据

总结一下副本同步流程：

CK进行插入操作 --> ZK中提交信息 --> ZK同步给其他节点 --> 其他节点得到通知 --> 其他节点从目标节点拉取数据 --> 实现副本数据同步

4.2 两个分片

一个分片有副本一个分片没有副本

-- lixnu201 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'linux201')
order by id;
-- lixnu202 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'linux202')
order by id;
-- lixnu203 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'linux203')
order by id;
----------------------------------------------------------------------------------------
-- lixnu201 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'linux201')
order by id ;
-- lixnu202 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/01/tb_demo2', 'linux202')
order by id ;
-- lixnu203 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/02/tb_demo2', 'linux203')
order by id ;
 
-- lixnu204 机器
create table tb_demo2 (
    id Int8 ,
    name String)engine=ReplicatedMergeTree('/clickhouse/tables/02/tb_demo2', 'linux203')
order by id ;

5.分布式引擎

Distributed表引擎是分布式表的代名词，它自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作，

img

一般使用分布式表的目的有两种，

  • 一种是表存储多个副本并且有大量的并发操作，我们可以使用分布式表来分摊请求压力解决并发问题

  • 一种是表特别大有多个切片组成 ，并且每切片数据也可以存储数据副本

  • 本地表：通常以_local为后缀进行命名。本地表是承接数据的载体，可以使用非Distributed的任意表引擎，一张本地表对应了一个数据分片

  • 分布式表：通常以_all为后缀进行命名。分布式表只能使用Distributed表引擎，它与本地表形成一对多的映射关系，日后将通过分布式表代理操作多张本地表。

ENGINE = Distributed(cluster, database, table [,sharding_key])

  • cluster：集群名称，与集群配置中的自定义名称相对应。在对分布式表执行写入和查询的过程中，它会使用集群的配置信息来找到相应的host节点。

  • database和table：分别对应数据库和表的名称，分布式表使用这组配置映射到本地表。

  • sharding_key：分片键，选填参数。在数据写入的过程中，分布式表会依据分片键的规则，将数据分布到各个host节点的本地表。如果没有指定则会生成随机数作为分片键，然后插入对应的host节点中。

5.1 没有副本

下面使用多个节点配置clickhouse的集群，可以在某台主机的metrika.xml文件中添加如下配置，配置完成后将配置文件同步到集群中的其他节点上，完成clickhouse的集群配置

<clickhouse_remote_servers>
    <!-- 集群名为cluster1 整个集群中每个表有三个分片, 分别在linux201 linux202 linux203上 -->
    <cluster1>
        <shard>
            <replica>
                <host>linux201</host>
                <port>9003</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>linux202</host>
                <port>9003</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>linux203</host>
                <port>9003</port>
            </replica>
        </shard>
    </cluster1>
    <!-- 集群名为cluster2 一个切片 三个副本 -->
    <cluster2>
        <shard>
            <replica>
                <host>linux201</host>
                <port>9003</port>
            </replica>
            <replica>
                <host>linux202</host>
                <port>9003</port>
            </replica>
            <replica>
                <host>linux203</host>
                <port>9003</port>
            </replica>
        </shard>
    </cluster2>
 
    <!-- 集群名为cluster3 多个分片 保留副本 注意一个主机只使用一次 -->
    <cluster3>
        <shard>
            <replica>
                <host>linux201</host>
                <port>9003</port>
            </replica>
            <replica>
                <host>linux202</host>
                <port>9003</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>linux203</host>
                <port>9003</port>
            </replica>
            <replica>
                <host>linux204</host>
                <port>9003</port>
            </replica>
        </shard>
    </cluster3>
</clickhouse_remote_servers>

习惯上，将上述内容配置在metrika.xml的zookeeper-servers标签之前

<?xml version="1.0"?>
<yandex>
    </clickhouse_remote_servers>
        <cluster1>
            ......
        </cluster1>
        <cluster2>
            ......
        </cluster2>
    </clickhouse_remote_servers>
    <zookeeper-servers>
        <node index="1">
             ......
        </node>
        <node index="2">
             ......
        </node>
    </zookeeper-servers>
</yandex>

将分布式表的配置同步后可以建立分布式表，ClickHouse分布式表的配置是热配置，无需重启Server

-- 同步配置文件 到集群中
-- 创建本地表
create table dbtest.tb_demo3 on cluster cluster1(
id  Int8 ,
name String
) engine=MergeTree()
order by id ;
-- 创建分布式表
create table dbtest.demo3_all on cluster cluster1
engine=Distributed('cluster1','dbtest','tb_demo3', id) as dbtest.tb_demo3;
-- 向分布式表中插入数据, 数据会根据插入规则将数据插入到不同的分片中
insert into dbtest.demo3_all values(1,'zss'),(2,'lss'),(3,'ww'),(4,'xx'),(5,'ee') ;
5.2 有副本的配置
<!-- 配置集群2 , 集群中的表有两个分片 ,其中分片1 有两个副本 -->
<cluster2>
    <shard>
        <replica>
            <host>linux201</host>
            <port>9000</port>
        </replica>
        <replica>
            <host>linux202</host>
            <port>9000</port>
        </replica>
    </shard>
    <shard>
        <replica>
            <host>linux203</host>
            <port>9000</port>
        </replica>
    </shard>
</cluster2>

-- 创建本地表
create table tb_demo4 on cluster cluster2(
id  Int8 ,
name String
)engine=MergeTree()
order by  id ;
-- 创建分布式表
create table demo4_all on cluster cluster2 engine=Distributed('cluster2','default','tb_demo4',id) as tb_demo4 ;

注意

  • 同一个cluster中不要出现相同的主机，即使在不同的分片上，因为这样会造成系统分不清副本属于哪个分片

总结

  • 数据单分片的时候 , 多个副本 可以不使用分布式表
  • 有多个分片的时候使用分布式表 给分片分配数据

具体使用场景

  • 多个分片的表
  • 多个副本表
  • 多分片 多副本：一个节点在一个集群中只能使用一次

6.分布式DDL

ClickHouse支持集群模式，一个集群拥有1到多个节点。CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行DDL的烦恼。将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。例如，执行下面的语句后将会对 ch_cluster集群内的所有节点广播这条DDL语句：

-- 建表 on cluster cluster1
create table tb_demo3 on cluster cluster1(
id  Int8 ,
name String
)engine=MergeTree()
order by id ;
-- 删除集群中所有的本地表或者是分布式表
drop table if exists tb_demo3 on cluster cluster1;
-- 修改集群中的表结构
alter table t3 on cluster cluster1 add column age Int8 ;

可以通过systems.clusters表查看ClickHouse的分布表情况

select * from systems.clusters;

7.分布式协同原理

副本协同的核心流程主要有INSERT、MERGE、MUTATION和ALTER四种，分别对应了数据写入、分区合并、数据修改和元数据修改。INSERT和ALTER查询是分布式执行的。借助 ZooKeeper的事件通知机制，多个副本之间会自动进行有效协同，但是它们不会使用ZooKeeper存储任何分区数据。而其他查询并不支持分布式执行，包括SELECT、CREATE、DROP、RENAME和ATTACH。例如，为了创建多个副本，我们需要分别登录每个ClickHouse节点。接下来，会依次介绍上述流程的工作机理。为了便于理解，我先来整体认识一下各个流程的介绍方法。

7.1 insert原理
image-20210522144000949
7.2 Merge原理

无论MERGE操作从哪个副本发起，其合并计划都会交由主副本来制定,和insert一样

img
7.3 mutation原理

alter table x update name=zss where

alter table x delete where

当对ReplicatedMergeTree执行ALTER DELETE或者ALTER UPDATE操作的时候，即会进入MUTATION部分的逻辑，它的核心流程如图

img
7.4 alter原理

当对ReplicatedMergeTree执行ALTER操作进行元数据修改的时候，即会进入ALTER部

分的逻辑，例如增加、删除表字段等。

img
mpmt91
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
C#学习笔记——基本语法
12-31
基本语法 C#，又名Csharp，天朝喜欢叫C井。 C#是一种面向对象的编程语言。在面向对象的程序设计方法中，程序有各种相互交互的对象组成。相同种类的对象通常具有相同的类型，或者说，是在先沟通那个的class中。 例如,...
Python学习笔记——大数据之Spark简介与环境搭建
02-24
Spark是Apache顶级项目里面最火的大数据处理的计算引擎，它目前是负责大数据计算的工作。包括离线计算或交互式查询、数据挖掘算法、流式计算以及图计算等。核心组件如下：SparkCore：包含Spark的基本功能；...
Clickhouse分布式集群搭建
大数据研习社
07-27 4164
这种方法更为安全，它可以把使用这些环境变量的权限控制到用户级别,这里是针对某一个特定的用户，如果你需要给某个用户权限使用这些环境变量，你只需要修改其个人用户主目录下的.bashrc文件就可以了。这里是针对所有的用户的,所有的shell。Zookeeper最小集群是3节点集群，生产环境中100节点以下集群3个Zookeeper节点就够用，500节点以下集群5节点就够用。可以到官网下载对应版本的jdk，这里选择安装jdk1.8版本，并上传至/home/hadoop/app目录下。...
clickhouse分布式集群搭建
微电子学与固体电子学-俞驰
06-19 3633
根据[1] Clickhouse 仅支持 Linux 操作系统，且机器 CPU 必须支持 SSE 4.2 指令集。 引擎 介绍 MergeTree 是CK里最Advanced的引擎，性能超高，单机写入可以达到50w峰值，查询性能非常快 ReplicatedMergeTree 基于MergeTree，同时引入ZK，做了复制 Distributed 分布式引擎，本身不存储数据，可认为就是一张View，如果写入，会把请求丢到集群里的节点（有算法控制），如果查询，会帮你做查询
Python3 使用 clickhouse_driver 操作 clickhouse
yudiandian2014的专栏
06-14 487
Python3 使用 clickhouse_driver 操作 clickhouse
ClickHouse分布式集群部署
怪只怪满眼尽是人间烟火
03-28 1833
前提： 首先每个集群上搭建zookeeper集群。 然后每个集群上都部署ClickHouse 集群部署： 每个集群上都新建一个文件：/etc/metrika.xml touch /etc/metrika.xml <?xml version="1.0" encoding="utf-8"?> <yandex> <clickhouse_remote_servers> <perftest_3shards_1replicas>
Doris：使用表函数explode实现array字段列转行
闫玉林的博客
03-15 1818
一句话总结：Doris宽表怎么转成高表，就是借助Lateral view。
ClickHouse版行转列，列转行PK Hive
start_hadoop的博客
05-23 3409
– 行转列，列转行 一： hive版本 孙悟空 白羊座 A 大海 射手座 A 宋宋 白羊座 B 猪八戒 白羊座 A 凤姐 射手座 A 苍老师 白羊座 B 结果如下： 射手座,A 大海|凤姐 白羊座,A 孙悟空|猪八戒 白羊座,B 宋宋|苍老师 SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name)) FROM ( SELECT NAME ,CONCAT_WS(’,’,constellatio
hadoop3自学入门笔记(2)—— HDFS分布式搭建
01-20
【Hadoop 3自学入门笔记(2)—— HDFS分布式搭建】 在学习Hadoop 3分布式文件系统（HDFS）的搭建过程中，首先要了解Hadoop 2与Hadoop 3的差异，特别是在端口配置方面。Hadoop 3相较于Hadoop 2在安全性、可扩展性和...
ClickHouse入门学习笔记
11-12
综上所述，学习ClickHouse涉及其基本概念、数据类型、存储结构、SQL语法、副本和集群管理、查询优化等多个方面，为大数据分析提供了强大且高效的解决方案。掌握这些知识点将有助于构建和优化ClickHouse系统，实现...
Centos单机伪分布式ClickHouse搭建
冰血ANG的博客
02-19 1525
ClickHouse版本：18.12.17 1、下载安装包 如下图所示： 安装命令：yum -y localinstall *.rpm 2、安装之后一些重要目录的说明 目录：/var/log/clickhouse-server 服务日志目录 目录：/etc/clickhouse-server 包含clickhouse的两个配置文件，config.xml和user.xml 脚本：/etc/init...
clickhouse-driver：具有本机界面支持的ClickHouse Python驱动程序
02-04
clickhouse-driver：具有本机界面支持的ClickHouse Python驱动程序
如何在clickhouse数据库中模拟慢查询
普普通通程序猿的博客
03-17 2314
clickhouse模拟生成慢查询的方法
Python使用ClickHouse实践与踩坑记
热门推荐
肖永威的专栏
06-17 2万+
本文为初步使用ClickHouse做OLAP数据分析实践，并记录了数据库连接端口配置、接口返回值等问题。体会到ClickHouse的速度快。
Python 连接clickhouse常用的三种方式
最新发布
qiuweifan的博客
06-21 1565
ClickHouse是一个开源的分布式列式数据库管理系统，它被设计用于存储和分析大规模数据。Python是一种流行的编程语言，凭借其简洁的语法和丰富的生态系统，成为了数据处理和分析的首选语言之一。在Python中，我们可以使用多种方式与ClickHouse进行连接并操作数据。本文将详细介绍Python连接ClickHouse的三种常用方式，分别是使用clickhouse-driver、clickhouse-sqlalchemy和pyclickhouse库。
explode() 函数的应用
余安
12-25 1558
explode() 函数把字符串分割为数组。 语法 explode(separator,string,limit) 参数 描述 separator 必需。规定在哪里分割字符串。 string 必需。要分割的字符串。 limit 可选。规定所返回的数组元素的最大数目。 /**
Clickhouse 基础知识 - 函数
weixin_30670965的博客
07-31 2394
部分基本函数SQL脚本示例： -------------------------------------------------- -------------------------------------------------- ---------------Clickhouse基础知识：函数学习------------- --官址学习文档：https://clickho...
关于python与clickhouse的连接问题
fairy_porridge的博客
07-29 1087
python连接clickhouse
向clickhouse导数据报错：clickhouse_driver.errors.UnexpectedPacketFromServerError: Code: 102
qq_40999942的博客
03-24 3521
今天用python写个etl从mysql向clickhouse同步数据， 数据量不到1千万，导了一会报上面的第错误。然后在网上查了一下：找到文档写得很清楚： Welcome to clickhouse-driver — clickhouse-driver 0.2.4 documentation 原来clickhouse提供两个端口：8123和9000 用native protocal需要使用9000端口，然后修改端口后成功。 ...
clickhouse 五（分布式表原理解析）
写bug的小哥哥
10-15 8338
1.Distributed引擎介绍 Distributed表引擎是分布式表的代名词，它⾃身不存储任何数据，⽽是作为数据分⽚的透明代理，能够⾃动的 路由数据⾄集群中的各个节点，即分布式表需要和其他数据表⼀起协同⼯作。分布式表会将接收到的读写任务， 分发到各个本地表，而实际上数据的存储也是保存在各个节点的本地表中。 原理如下图： 2.创建分布式表 使用on cluster语句在集群的某台机器上创建一下代码，即可在每台机器上创建本地表和分布式表， ⼀张本地表对应着⼀个数据分⽚，分布式表通常以本地表加“_al
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值