大数据之clickhouse常用函数

一、查询语法

wih语法

with 1 as num select num+1  --定义变量

with toYear(now()) as y select * from t_user where ct=y     --调用函数

with (select * from log2 where uid=4) as x select *, x from  log2    --定义子查询,子查询只能返回一行结果

with模型

with cube:

select province, city, area, sum(visit) from  tb_with  group by province, city, area with cube

with rollup: 上卷模型

select province, city, area, sum(visit) from  tb_with  group by province, city, area with rollup

with totals:

select province, city, area, sum(visit) from  tb_with  group by province, city, area with totals

From语法

select * from  hdfs('hdfs://doit01:8020/click/user.txt','CSV', 'id UInt16 ,name String')

select toDate('2021-01-01') + number from number(365) 

Array join语法

从而将一行 数组展开为多行。类似于hive中的explode炸裂函数的功能!

create table tb_arr_join(
	id Int8 ,
	name String ,
	hobby Array(String)
)engine=Log ;

insert  into tb_arr_join  values (1,'wbb',['eat','drink','piao']) ,(2,'naige',['eat','chou','piao','piaole']) ;

查询：

select id , name ,hobby ,arrayEnumerate(hobby) as idxfrom tb_arr_join ;

┌─id─┬─name──┬─hobby──────────────────────────┬─idx───────┐
│  1 │ wbb   │ ['eat','drink','piao']         │ [1,2,3]   │
│  2 │ naige │ ['eat','chou','piao','piaole'] │ [1,2,3,4] 

select  id , name ,hobby ,arrayEnumerate(hobby) as idx ,h ,i from tb_arr_join  array  join  hobby as h  , idx  as  i ;

┌─id─┬─name──┬─hobby──────────────────────────┬─idx───────┬─h──────┬─i─┐
│  1 │ wbb   │ ['eat','drink','piao']         │ [1,2,3]   │ eat    │ 1 │
│  1 │ wbb   │ ['eat','drink','piao']         │ [1,2,3]   │ drink  │ 2 │
│  1 │ wbb   │ ['eat','drink','piao']         │ [1,2,3]   │ piao   │ 3 │
│  2 │ naige │ ['eat','chou','piao','piaole'] │ [1,2,3,4] │ eat    │ 1 │
│  2 │ naige │ ['eat','chou','piao','piaole'] │ [1,2,3,4] │ chou   │ 2 │
│  2 │ naige │ ['eat','chou','piao','piaole'] │ [1,2,3,4] │ piao   │ 3 │
│  2 │ naige │ ['eat','chou','piao','piaole'] │ [1,2,3,4] │ piaole │ 4 │
└────┴───────┴────────────────────────────────┴───────────┴────────┴───

每个店铺最高连续销售记录

准备数据：

│ a    │ 2017-02-05 │   200 │
│ a    │ 2017-02-06 │   300 │
│ a    │ 2017-02-07 │   200 │
│ a    │ 2017-03-03 │   200 │
│ b    │ 2017-02-05 │   200 │
│ b    │ 2017-02-08 │   200 │
│ b    │ 2017-02-09 │   400 │
│ b    │ 2017-02-10 │   600 │
│ c    │ 2017-01-31 │   200 │
│ c    │ 2017-02-03 │   400 │
│ c    │ 2017-02-10 │   600 

建表导入数据

create  table  tb_shop (
  name String ,
  cdate Date ,
  money Float64
)engine=MergeTree()
order by (name , cdate) ;

clickhouse-client  -q 'insert into tb_shop FORMAT CSV' < /data/shop.txt

编写函数

select
   name ,
   (ct-arr_index) diff,
   count(1) as cc
from
    (select
       name ,
       groupArray(cdate)  arr ,
       arrayEnumerate(arr) idx
    from
       tb_shop 
    group by name)
array join
arr as ct ,
idx as arr_index
group by name,diff
order by cc desc
limit 1 by name

结果：

Join语法

连接精度决定了JOIN查询在连接数据时所使用的策略，目前支持all、any和asof三种类型，默认是all类型

all: 如果左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据。相等于内关联

any: 如果左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据.

asof: 是一种模糊连接，它允许在连接键之后追加定义一个模糊连接的匹配条件.

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');

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');

select * from emp1 asof inner join emp2 on emp1.id = emp2.id  and  emp1.ctime > emp2.ctime;

┌─id─┬─name─┬───────ctime─────────┬─emp2.id─┬─emp2.name─┬──────emp2.ctime─────┐
│  1 │ AA   │ 2021-01-03 00:00:00 │    1    │    aa     │ 2021-01-02 00:00:00 │

二、常用函数

json解析案例

创建源表

创建主题表

三、分布式简介

数据副本

如果在*MergeTree的前面增加Replicated的前缀，则能够组合成一个新的变种引擎，即Replicated-MergeTree复制表，只有使用了ReplicatedMergeTree复制表系列引擎，才能应用副本的能力。

分布式部署：

1、在每天机器上安装clickhouse, 安装步骤见 clickhouse安装部署

2、允许其他机器访问，

vi config.xml

<listen_host>::</listen_host>

3、配置zookeeper

/etc/clickhouse-server/config.d, 创建metrika.xml的配置文件

<?xml version="1.0"?>
<yandex>
 <zookeeper-servers> 
 <node index="1"> 
 <host>ck1</host>
 <port>2181</port>
 </node>
  <node index="2"> 
 <host>ck2</host>
 <port>2181</port>
 </node>
  <node index="3"> 
 <host>ck3</host>
 <port>2181</port>
 </node>
 </zookeeper-servers>
</yandex>

在全局配置config.xml中使用<include_from>标签导入刚才定义的配

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

配置zookeeper生效

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

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

分布式配置副本

1、一个分片，多个副本

在节点1上创建的表和数据，其他节点上也能查到

2、多分片，没有副本

/etc/clickhouse-server/config.d/metrika.xml文件中，添加

建表：

分布式表：

因为副本设置的是1，所以通过 t2_all 插入数据，会存到任意一个机器上；从别的机器上，插入数据，t2_all 也能查到

3、一个分片，多个副本

<cluster2>
 <shard>
	<replica>
		<host>doit01</host>
		<port>9000</port>
	</replica>
    <replica>
		<host>doit02</host>
		<port>9000</port>
	</replica>
 </shard>
 <shard>
	<replica>
		<host>doit03</host>
		<port>9000</port>
	</replica>
 </shard>
</cluster2>

建表

create table t4 on cluster cluster2(
	id  Int8 ,
	name String 
)engine=MergeTree() ;

4、多个分片，多个副本

<cluster3>
 <shard>
	<replica>
		<host>doit01</host>
		<port>9000</port>
	</replica>
    <replica>
		<host>doit02</host>
		<port>9000</port>
	</replica>
 </shard>
 <shard>
	<replica>
		<host>doit03</host>
		<port>9000</port>
	</replica>
	<replica>
		<host>doit04</host>
		<port>9000</port>
	</replica>
 </shard>
</cluster3>

分布式表：

总结：
数据只有1个分片，多个副本的时候，可以不使用分布式表；
数据有多个分片， 使用分布式表，给分片分配数据

5、分布式DDL