【用户画像】ClickHouse中的SQL操作、副本介绍和配置、分片集群环境配置和使用

一 SQL操作

基本上来说传统关系型数据库（以MySQL为例）的SQL语句，基本支持但是也有不一样的地方。这里只介绍Clickhouse与标准SQL（MySQL）不一致的地方。

1 Insert

基本与标准SQL（MySQL）基本一致

包括标准 insert into [table_name] values(…),(….)

以及 从表到表的插入

  insert into  [table_name] select a,b,c from [table_name_2]

2 Update 和 Delete

ClickHouse提供了Delete 和Update的能力，这类操作被称为Mutation查询，它可以看做Alter 的一种。

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

删除操作

alter table t_order_smt delete where sku_id ='sku_001';

修改操作

alter table t_order_smt 
update total_amount=toDecimal32(2000.00,2) 
where uid = 102;

由于操作比较“重”，所以 Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。

3 查询操作

clickhouse基本上与标准SQL 差别不大。

• 支持子查询

• 支持CTE(with 子句)

• 支持各种JOIN， 但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，Clickhouse也会视为两条新SQL。

• 不支持窗口函数。

• 不支持自定义函数。

  GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

模拟数据

insert into  t_order_mt
values(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(101,'sku_004',2500.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00')
(101,'sku_003',600.00,'2020-06-02 12:00:00'),
(110,'sku_001',1000.00,'2020-06-04 12:00:00'),
(110,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_004',2500.00,'2020-06-04 12:00:00'),
(110,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00')

select uid,sku_id,sum(total_amount) from  t_order_mt group by uid,sku_id with rollup;

with rollup : 从右至左去掉维度进行小计【上卷】。

结果如图：

select uid,sku_id,sum(total_amount) from  t_order_mt group by uid,sku_id with cube;

with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计。

结果如图：

select uid,sku_id,sum(total_amount) from  t_order_mt group by uid,sku_id with totals;

with totals: 只计算合计。

4 alter操作

同mysql的修改字段基本一致，

新增字段

alter table tableName  add column  newcolname String after col1

after col1 是指在哪个字段后面加新字段

修改字段类型

alter table tableName  modify column  newcolname String ;

删除字段

alter table tableName  drop column  newcolname;

5 导出数据

即席查询，将数据导出为一个文件。

clickhouse-client  --query "select toHour(create_time) hr  ,count(*) from test1.order_wide where dt='2020-06-23'  group by hr" --format CSVWithNames> ~/rs1.csv

如

[hzy@hadoop101 ~]$ clickhouse-client  --query "select uid,sku_id,sum(total_amount) from  test.t_order_mt group by uid,sku_id with cube" --format CSVWithNames> ~/rs1.csv

文件内如如下

"uid","sku_id","sum(total_amount)"
110,"sku_003",1200.00
101,"sku_003",600.00
101,"sku_002",4000.00
101,"sku_004",2500.00
101,"sku_001",1000.00
110,"",1200.00
101,"",8100.00
0,"sku_003",1800.00
0,"sku_004",2500.00
0,"sku_001",1000.00
0,"sku_002",4000.00
0,"",9300.00

二 副本（高可用）

副本的目的主要是保障数据的高可用性，即使一台clickhouse节点宕机，那么也可以从其他服务器获得相同的数据。

1 副本写入流程

2 副本配置

• 这时需要启动zookeeper集群 和另外一台clickhouse 服务器
• 另外一台clickhouse服务器的安装完全和第一台一直即可。
• 在两台服务器的/etc/clickhouse-server/config.d目录下创建一个名为metrika.xml的配置文件（声明zookeeper的集群）：

<?xml version="1.0"?>
<yandex>
  <zookeeper-servers>
     <node index="1">
	     <host>hadoop101</host>
		 <port>2181</port>
     </node>
	 <node index="2">
	     <host>hadoop102</host>
		 <port>2181</port>
     </node>
     <node index="3">
	     <host>hadoop103</host>
		 <port>2181</port>
     </node>
  </zookeeper-servers>
</yandex>

在 /etc/clickhouse-server/config.xml 中增加

<zookeeper incl