最全的clickhouse梳理

拼搏现实的明天。 2023-03-13 02:14 25阅读 0赞

**下面的相关代码，全是基于生产环境部署实践的，**

\---客户端链接工具  
clickhouse-client -m -u root -p root

\--大小写敏感的  
CREATE TEMPORARY TABLE IF NOT EXISTS test.member\_new\_simple2 ENGINE = Memory   
AS SELECT ID,user\_id,member\_grade,accum\_amount from  test.member\_new;

\-- SHOW PROCESSLIST  
查询正在进行执行的sql操作

关于 update/delete操作

clickhouse关于update/delete操作是异步后台进行,而且成本很高,所以尽量减少和避免进行update/delete操作

通过 select \* from system.mutations    可以判断后台操作进行是否完成

\-------------- create --------------------   
create TABLE test.test( id Int32,create\_date Date ,c2 Nullable(String) )   
engine=MergeTree(create\_date,id,(c3,c2),8192);

create TABLE test.test2( id Int32,create\_date Date ,c2 Nullable(String) )   
engine=MergeTree(create\_date,id,8192);

\--支持联合索引  
MergeTree(EventDate, (CounterID, EventDate), 8192)

MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

CREATE TABLE t (a UInt8) ENGINE = Memory;

split -b 20g table\_name.sql user\_group

\-------------- ALTER--------------------   
ALTER查询只支持MergeTree表，。该查询有几个变体。  
ALTER TABLE \[db\].name \[ON CLUSTER cluster\] ADD|DROP|MODIFY COLUMN  
\--更改列的类型

alter TABLE test.ontime\_wpp\_t MODIFY COLUMN TailNum Nullable(String);

alter TABLE test.ontime\_wpp\_t ADD COLUMN TailNum2 Nullable(String)  after   Div5TailNum ;

alter TABLE test.ontime\_wpp\_t drop COLUMN TailNum2;

表变更数据系统监控  
select \* from system.mutations where is\_done = 0 order by create\_time desc limit 1;

删除表  
drop table cdm\_dwd.dwd\_ord\_car\_sharing\_df on cluster crm\_4shards\_1replicas;

select max(ckbizdate) from cdm\_dwd.dwd\_mkt\_coupon\_ord\_df

变更表名  
RENAME TABLE test.ontime\_wpp\_t to test.ontime\_wpp\_t2;

集群操作

RENAME TABLE cdm\_dwd.dwd\_ord\_carsh\_base\_df2 to cdm\_dwd.dwd\_ord\_carsh\_base\_df on cluster crm\_4shards\_1replicas;

SET param = value  
允许您设置param值，如果指定了全局，则为会话或服务器（全局）设置设置。  
在创建全局设置时，设置并不适用于已经运行的会话，包括当前会话。它只会用于新会话。当服务器重新启动时，  
使用SET的全局设置就会丢失。要在服务器重新启动后进行设置

OPTIMIZE table test.ontime;

仅支持MergeTree引擎， 用于回收闲置的数据库空间，当表上的数据行被删除时，  
所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，  
并且对磁盘上的数据行进行重排

数据导出  
在相关节点执行  
echo 'select \* from test.ads\_user\_portrait\_vertical\_df\_cls' | curl localhost:8123?database=test -uroot:root -d @- > table\_name.sql

2、导入数据，以tab作为分割符：

导入数据库的本机执行：cat table\_name.sql | clickhouse-client --query="INSERT INTO database.table\_name FORMAT TabSeparated"  
       
       
cat /root/user\_lable\_local\_mid\_cluster.tgz | clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.user\_lable\_local\_mid\_cluster FORMAT CSV";  
cat /home/hadoop/work\_wpp/user\_label\_uid\_cluster | clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.user\_label\_uid\_cluster FORMAT CSV";

cat /tmp/test\_user2| clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.test\_user2 FORMAT CSV";

\-------------- INSERT--------------------   
不严格插入数据，没有出现的列自动填充为默认值  
INSERT INTO \[db.\]table \[(c1, c2, c3)\] VALUES (v11, v12, v13), (v21, v22)

严格插入数据，每一列都必须出现在上面  
INSERT INTO \[db.\]table \[(c1, c2, c3)\] FORMAT Values (v11, v12, v13), (v21, v22, v23)

cat /tmp/user\_point\_info | clickhouse-client --query="INSERT INTO test.user\_point\_info FORMAT CSV";

cat /home/hadoop/ontime | clickhouse-client  --query="INSERT INTO test.ontime\_wpp FORMAT CSV";

cat /root/wpp5.log | clickhouse-client --query="INSERT INTO test.test FORMAT CSV";

clickhouse-client -m --user hadoop --password hadoop --query="truncate table lmmbase.user\_label\_uid on cluster crm\_4shards\_1replicas";

ssh hadoop@dn1 "/bin/bash /home/hadoop/app/otherApp/truncate\_user\_label\_uid\_data.sh"

clickhouse-client --query=" alter table  test.ads\_user\_portrait\_vertical\_df delete where create\_time ='2019-10-17' ";

clickhouse-client --query=" alter table  default.test\_df delete where ckbizdate ='2019-10-17' ";

相关压测，同时执行相关sql，看下机器负载  
\*/2 \* \* \* \* clickhouse-client -m --query="select t\_mac,t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_mac,t\_type order by cnt desc limit 100;"  
\*/2 \* \* \* \* clickhouse-client  -m --query="select t\_mac,count(\*) cnt from carendpoint\_porlog\_cls group by t\_mac order by cnt desc limit 100;"  
\*/2 \* \* \* \* clickhouse-client  -m --query="select t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_type order by cnt desc limit 100;"

\*/1 \* \* \* \* clickhouse-client  -m --query="select t\_ip,t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_ip,t\_type order by cnt desc limit 100;" >> /root/wpp1.log  
\*/1 \* \* \* \* clickhouse-client  -m --query="select t\_ip,count(\*) cnt from carendpoint\_porlog\_cls group by t\_ip order by cnt desc limit 100;" >> /root/wpp2.log  
\*/1 \* \* \* \* clickhouse-client  -m --query="select event,count(\*) cnt from carendpoint\_porlog\_cls group by event order by cnt desc limit 100;" >> /root/wpp2.log

直接支持hdfs

drop table test\_hdfs;  
CREATE  TABLE test\_hdfs  
(  
rowid Int64,  
create\_date String,  
memo String  
)  
ENGINE=HDFS('hdfs://gtdata03:8020/input/test2/test2.txt','CSV');

sql 文件执行

clickhouse-client -h  127.0.0.1 -m -d system -q "select \* from parts " > test.sql

dn4  
clickhouse-client -h  127.0.0.1 -m -d system -q '/home/hadoop/ch.sql' > test.sql

select today()-365;

drop table lmmbase.user\_label\_uid\_hive\_cluster on cluster crm\_4shards\_1replicas;

drop table lmmbase.user\_label\_uid\_hive on cluster crm\_4shards\_1replicas;

create database  on cluster crm\_4shards\_1replicas;

row\_number 函数支持,下面是支持 !!!!!!!!!!!!!  
select \*,rowNumberInAllBlocks() rn  
from(  
select id,create\_date,c2  
from test   
order by  create\_date desc   
limit 2 by id  
)aa ;

SELECT \*, rn +1 -min\_rn current, max\_rn - min\_rn + 1 last FROM (  
SELECT \*, rowNumberInAllBlocks() rn FROM (  
SELECT i\_device, i\_time  
FROM tbl  
ORDER BY i\_device, i\_time  
) t  
) t1 LEFT JOIN (  
SELECT i\_device, min(rn) min\_rn, max(rn) max\_rn FROM (  
SELECT \*, rowNumberInAllBlocks() rn FROM (  
SELECT i\_device, i\_time  
FROM tbl  
ORDER BY i\_device, i\_time  
) t  
) t GROUP BY i\_device  
) t2 USING (i\_device)

select trainId,ap\_mac,  
 rowNumberInAllBlocks() rn  
from carendpoint\_usermacprobe   
group by trainId,ap\_mac  
limit 10;

select trainId,ap\_mac,  
rowNumberInAllBlocks() rn  
from carendpoint\_usermacprobe   
group by trainId,ap\_mac  
order by  trainId desc   
limit 3 by trainId;

dn3

select id,create\_date,c2,  
rowNumberInAllBlocks() rn  
from test   
group by  id,create\_date,c2  
order by  create\_date desc   
limit 3 by id;

select \*,rowNumberInAllBlocks() rn  
from(  
select id,create\_date,c2  
from test   
group by  id,create\_date,c2  
order by  create\_date desc   
limit 2 by id  
)aa ;

insert into test select 11,'2019-07-25','tt5';

select trainId,ap\_mac,  
ROW\_NUMBER() OVER (PARTITION BY trainId order by trainId  ) AS  row   
from carendpoint\_usermacprobe limit ;

\----------------------delete   
删除表的数据，对主键支持的可以，非主键有时数据删除有问题  
alter table user\_label\_uid\_hive delete where id = 1000000010002   
alter table user\_label\_uid\_hive delete where id = 1000000010002

truncate table ads\_user\_portrait\_vertical\_df\_cls   ON CLUSTER crm\_4shards\_1replicas;

\-- 数据去重  
select user\_id, order\_id, create\_time, update\_time from unity\_order   
where create\_time> '2018-09-01' order by create\_time asc limit 2 by user\_id limit 20;

insert into user2( ROWKEY2, AV, IMEI, MAC, CHID, PB, PLAT,UID,PTIME) values  
( "20190905", "2.1.6", "54b6aeba8581413ca487eda6caa62a55", "7c:76:68:19:34:16", "NULL22", "HONOR", "2", "tt", "2019-09-05");  
,( "20190905\_USERID257641565864162599", "1.0.0", "USERID257641565864162599", "other", "other", "iPhone", "3", "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJhY2NvdW50SWQiOiIxMTA0OTQzMTM5Mjg3ODU5MjAwIiwibW9iaWxlIjoiMTc2ODIzMDIwNDkiLCJ0eXAiOiJKV1QiLCJleHAiOjE1Njg1Mjc3MDgsImNvcnBDb2RlIjoiQ1JHVCIsImFsZyI6IkhTMjU2IiwiaWF0IjoxNTY3NjYzNzA4LCJsb2dpblNvdXJjZSI6IjEifQ.tE\_59BIsNqGdbUe5HslFZ93\_vyNQE2EUHwvlyOkCQhc", "2019-09-05")

CREATE TABLE test.user2 (\`ROWKEY2\` String, \`AV\` String, \`IMEI\` String, \`MAC\` String, \`CHID\` String, \`PB\` String, \`PLAT\` String, \`UID\` String, \`PTIME\` Date) ENGINE = MergeTree(PTIME, ROWKEY2, 8192)

insert into user2( ROWKEY2, AV, IMEI, MAC, CHID,  PB, PLAT,UID,PTIME) values  
("20190905", "2.1.6", "54b6aeba8581413ca487eda6caa62a55", "7c:76:68:19:34:16",  "ttt", "HONOR", "2", "tt", "2019-09-05");

alter table user\_label\_uid\_hive delete where id = 1000000010002

alter table user\_label\_uid\_hive delete where user\_id = 10035

select id,user\_id from  user\_label\_uid\_hive  where user\_id = 10035

alter table user\_label\_uid\_hive update browse\_category\_id = 11 where mobile\_number in (13482154101) ;

alter table carendpoint\_porlog\_cls update utime = 999 where t\_mac='VIVO-Y55A';

select count(\*) from carendpoint\_porlog\_cls where t\_mac='VIVO-Y55A' limit 10;

select \* from carendpoint\_porlog where t\_mac='VIVO-Y55A' limit 10;

alter table carendpoint\_porlog update user\_agent = '999' where t\_mac='VIVO-Y55A' ;

alter table carendpoint\_porlog update user\_agent = '666' where utime=-7200 and t\_type='MI8-xiaomishouji';  
OPTIMIZE table carendpoint\_porlog;

select \* from carendpoint\_porlog where utime=-7200 and t\_type='MI8-xiaomishouji'

更新时加上索引优化。这样查询才快点，十亿级别更新，十秒完成。

alter table carendpoint\_porlog update user\_agent = '999000' where t\_mac='VIVO-Y55A' ;

alter table carendpoint\_porlog delete where t\_mac='VIVO-Y55A' ;

alter table carendpoint\_porlog delete where utime=-7200  ;

select \* from carendpoint\_porlog where t\_mac='VIVO-Y55A' limit 10;

select count(\*) from carendpoint\_porlog where t\_mac='VIVO-Y55A' limit 10;

alter table user\_label\_uid\_hive update browse\_category\_id = 19999 where  browse\_category\_id in (18880);  
select count(\*) from  user\_label\_uid\_hive  where browse\_category\_id in (19999);

alter table user\_label\_uid\_hive update browse\_category\_id = 11888 where  browse\_category\_id in (11);  
select count(\*) from  user\_label\_uid\_hive  where browse\_category\_id in (11888);

alter table user\_label\_uid\_hive delete where mobile\_number in (13028815355) ;

\--- 直接所有表数据删除

truncate table lmmbase.user\_label\_uid on cluster crm\_4shards\_1replicas;

create database test on cluster crm\_4shards\_1replicas;

truncate table default.t22\_cluster on cluster 'crm\_4shards\_1replicas';

alter table cdm\_dws.dws\_log\_full\_di delete where gbizdate = '20190930'  on cluster crm\_4shards\_1replicas;

alter table cdm\_dws.dws\_log\_full\_di  on cluster crm\_4shards\_1replicas delete where gbizdate = '20190930'  ;

drop  table cdm\_dwd.dwd\_ord\_carsh\_base\_df\_cls on cluster crm\_4shards\_1replicas;

rename table cdm\_dwd.dwd\_ord\_carsh\_base\_df\_cls on cluster crm\_4shards\_1replicas;

create TABLE default.t3 ( id Int32,create\_date Date ,c2 String,c3 Nullable(String) )  
engine=MergeTree(create\_date,id,8192);

create TABLE default.t31 ON CLUSTER crm\_4shards\_1replicas( id Int32,create\_date Date ,c2 String,c3 Nullable(String) )  
engine=MergeTree(create\_date,id,8192);

create TABLE default.t3 on cluster crm\_4shards\_1replicas ( id Int32,create\_date Date ,c2 Nullable(String) )  
engine=MergeTree(create\_date,id,(c3,c2),8192);

create table default.t3 on cluster crm\_4shards\_1replicas (id UInt8, name String, t Date)    
engine = MergeTree(t, id, id, 2);

create table default.t3\_cluster on cluster crm\_4shards\_1replicas (id UInt8, name String, t Date)    
engine = Distributed('crm\_4shards\_1replicas', 'default', 't3', rand());

\--------------------cxw --直接建表

drop  DATABASE db2 ON CLUSTER crm\_4shards\_1replicas;

truncate table default.t1 on cluster crm\_4shards\_1replicas;

truncate table lmmbase.test\_user2 on cluster crm\_4shards\_1replicas;

truncate table lmmbase.user\_label\_uid\_hive on cluster crm\_4shards\_1replicas;

create TABLE default.porlog\_test on cluster crm\_4shards\_1replicas ( mac String ,train\_id Nullable(String),create\_date Date) engine=MergeTree(create\_date,mac,8192);

create table default.porlog\_test\_cls on cluster crm\_4shards\_1replicas ( mac String ,train\_id Nullable(String),create\_date Date )   
engine = Distributed('crm\_4shards\_1replicas', 'default', 'porlog\_test', rand());

create TABLE test.test2( id Int32,create\_date Date ,c2 Nullable(String) )   
engine=MergeTree(create\_date,id,8192);

insert into default.test\_cluster values  
(11,'2018-10-10','11'),  
(22,'2018-10-11','22'),  
(33,'2018-10-11','33');

select id,create\_date,c2,any(c3) from default.test\_cluster;

select id,create\_date,c2 from default.test\_cluster where id<5  
union all  
select id,create\_date,c2 from default.test\_cluster where id>11

drop table crm\_pub.prod\_startdistrict\_detail\_cluster  on cluster crm\_4shards\_1replicas;

drop table test.user\_point\_info2\_cls  on cluster crm\_4shards\_1replicas;

drop table crm\_pub.biz\_category\_cluster  on cluster crm\_4shards\_1replicas;

drop table lmmbase.user\_label\_uid\_hive  on cluster crm\_4shards\_1replicas;

drop database crm\_user  on cluster crm\_4shards\_1replicas;

drop database crm\_market  on cluster crm\_4shards\_1replicas;

create database cdm\_dws  on cluster crm\_4shards\_1replicas;

drop database test  on cluster crm\_4shards\_1replicas;

drop table lmmbase.user\_label\_uid\_hive  on cluster crm\_4shards\_1replicas;

drop table lmmbase.user\_label\_uid\_hive\_cluster  on cluster crm\_4shards\_1replicas;

clickhouse-client -m

\---------------------------------output--------------------    
select \* from test.test\_user2 INTO OUTFILE '/tmp/test\_user2' format CSV;

select \* from test.user\_point\_info limit 500000 INTO OUTFILE '/tmp/user\_point\_info' format CSV;

\-- 复杂表查询

drop TABLE l.user\_orders;

create TABLE test.user\_orders( order\_id Int64,create\_time Date,user\_id Nullable(String),  
user\_name Nullable(String),order\_status Nullable(String),user\_status Nullable(String) )   
engine=MergeTree(create\_time,order\_id,8192);

insert into test.user\_orders   
select distinct order\_id,create\_time,user\_id,user\_name,order\_status,user\_status  
from(   
select order\_id, user\_no AS user\_id,order\_status,toDate(create\_time) as create\_time     
from test.unity\_order )   
ANY LEFT JOIN  
(select user\_id,user\_name,user\_status,  
mobile\_number from  default.user\_user )   
USING user\_id ;

\-------------------------select  相关格式代码-------------------------

\--- union all

select id,create\_date,c2 from test.test where id<5  
union all   
select id,create\_date,c2 from test.test where id>11;  
select groupArray(variable\_id) from test.seo\_test limit 10;

\-- TSKV  
SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM ontime   
WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier FORMAT TSKV;

\--csv 逗号分割  
SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM ontime   
WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier FORMAT CSV;

\--JSON 逗号分割,可用于 http接口调用  
SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM ontime   
WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier FORMAT JSON;

\--XML 逗号分割,可用于 http接口调用  
SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM ontime   
WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier FORMAT XML;

\---生成的数据打包成json 数据格式  
select user\_id,groupArray(order\_id) from test.unity\_order GROUP BY user\_id limit 10;

wget -O- -q 'http://localhost:8123/?query=SELECT 1'

\----- http 请求查询结果

echo 'SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM test.ontime WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier' | curl 'http://localhost:8123/?query=' --data-binary @-  
格式化返回结果  
echo 'SELECT Carrier, avg(DepDelay > 10) \* 1000 AS c3 FROM test.ontime WHERE Year = 2007 GROUP BY Carrier ORDER BY Carrier  FORMAT Pretty' | curl 'http://localhost:8123/?query=' --data-binary @-

echo 'CREATE TABLE t1 (a UInt8) ENGINE = Memory' | POST 'http://localhost:8123/'  
echo 'insert into t1 (a) values (10)' | POST 'http://localhost:8123/'  
GET "http://localhost:8123?query=select \* from t"  
echo 'drop table t1' | POST 'http://localhost:8123/'

jdbc 方式连接  
https://github.com/yandex/clickhouse-jdbc

\-----web访问界面  
https://github.com/tabixio/tabix

clickhouse作为目前所有开源MPP计算框架中计算速度最快的，它在做多列的表，同时行数很多的表的查询时，性能是很让人兴奋的，但是在做表的join时，它的性能是不如单宽表查询的，但是了解了clickhouse在做join的时候的处理过程，利用的好的话，也会带来很大的效率提升，下面就详细介绍一下：

1.clickhouse做LEFT JOIN时的操作过程

比如做两表JOIN时，会优先计算右表结果，将右表结果存储到内存中，然后跟左边表的数据进行做match

2.join操作的秒级产出

首先，clickhouse在做单表查询时速度是快的惊人的，因此只要左表和右表查出来的数据量是不大的，那么整个查询响应时间仍然会是秒级产出的。

\-- ANY LEFT JOIN -- 匹配一条则结束  
select distinct order\_id,user\_id,user\_name,user\_status,  
mobile\_number as mobile,order\_status   
from(   
select order\_id, user\_no AS user\_id,order\_status     
from test.unity\_order where order\_id in(55719970 ,55720002)  
) ANY LEFT JOIN  
(select user\_id,user\_name,user\_status,  
mobile\_number from  default.user\_user )   
USING user\_id FORMAT CSV;

\-- ALL LEFT JOIN   
select distinct order\_id,user\_id,user\_name,user\_status,  
mobile\_number as mobile,order\_status   
from(   
select order\_id, user\_no AS user\_id,order\_status     
from test.unity\_order where order\_id in(55719970 ,55720002)  
) ALL LEFT JOIN  
(select user\_id,user\_name,user\_status,  
mobile\_number from  default.user\_user )   
USING user\_id FORMAT CSV;

\-- 三个表相连  
   
select distinct order\_id,user\_id,user\_name,user\_status,  
mobile\_number as mobile,order\_status,send\_time,send\_id  
from (  
    select distinct order\_id,user\_id,user\_name,user\_status,  
    mobile\_number,order\_status   
    from(   
    select order\_id, user\_no AS user\_id,order\_status     
    from test.unity\_order\_cluster where order\_id in(55719970 ,55720002)  
    ) ANY INNER JOIN  
    (select user\_id,user\_name,user\_status,  
    mobile\_number from  default.user\_user )  
    using user\_id  
)  
ANY left JOIN   
(select target as mobile\_number,send\_time,id as send\_id  
from  test.mkt\_sms\_log\_cluster )   
USING mobile\_number  
FORMAT CSV;

\---------------array join   
\-- https://clickhouse.yandex/docs/en/query\_language/select/  
\--------------- array join   
CREATE TABLE arrays\_test (s String, arr Array(UInt8)) ENGINE = Memory;

INSERT INTO arrays\_test VALUES ('Hello', \[1,2\]), ('World', \[3,4,5\]), ('Goodbye', \[\]);  
INSERT INTO arrays\_test VALUES ('Goodbye2', \[23\]);  
SELECT \* FROM arrays\_test;

SELECT s, arr FROM arrays\_test ARRAY JOIN arr;  
SELECT s, arr, a FROM arrays\_test ARRAY JOIN arr AS a;

\-- 针对map进行相加操作  
\-- 相同大小的多个数组可以在数组连接子句中进行逗号分隔。在这种情况下，连接是同时执行的  
SELECT s, arr, a, num, mapped   
FROM arrays\_test ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 2, arr) AS mapped;

SELECT s, arr, a, num, arrayEnumerate(arr)   
FROM arrays\_test ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num

\---------------nested join

CREATE TABLE nested\_test (s String, nesta Nested(x UInt8, y UInt32)) ENGINE = Memory;  
INSERT INTO nested\_test VALUES ('Hello', \[1,2\], \[10,20\]),   
('World', \[3,4,5\], \[30,40,50\]), ('Goodbye', \[\], \[\]);  
INSERT INTO nested\_test VALUES ('Goodbye\_nest', \[3,3\], \[101,201\]);

SELECT \* FROM nested\_test;  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta;

针对某个进行查询  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta where nesta.x=3;

\--一个用户下挂了多个订单  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta where s='Hello';

\--对x值进行拆分  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta.x;

\--对x值进行拆分  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta.y;

\-- 对x，y进行拆分  
SELECT s, nesta.x, nesta.y FROM nested\_test ARRAY JOIN nesta.x, nesta.y;

\---- 对数组进行枚举  
SELECT s, num FROM nested\_test  ARRAY JOIN arrayEnumerate(nesta.x) as num;

\--  
SELECT s, n.x, n.y, nesta.x, nesta.y, num FROM nested\_test   
ARRAY JOIN nesta AS n, arrayEnumerate(nesta.x) as num;

\------------------------nested join 2级嵌套 ----------------------------------

\---  
CREATE TABLE nested\_test2 (s String, nest1 Nested(x UInt8, y UInt32),nest2 Nested(x2 Int64,y2 Int64))  
 ENGINE = Memory;  
INSERT INTO nested\_test2 VALUES ('Hello',\[1,2\],\[10,20\],\[11,21\],\[11,21\]),  
('World', \[3,4,5\], \[30,40,50\],\[31,41,51\], \[301,401,501\]), ('Goodbye', \[\], \[\], \[\], \[\]),('Goodbye\_nest', \[3,3\], \[101,201\],\[31,31\], \[1011,2011\]);

INSERT INTO nested\_test2 VALUES ('Goodbye\_nest', \[3,3\], \[101,201\],\[31,31\], \[1011,2011\]);

SELECT \* FROM nested\_test2;  
SELECT s, nest1.x, nest1.y,nest2.x2 FROM nested\_test2 ARRAY JOIN nest1;

针对某个进行查询  
SELECT s, nest1.x, nest1.y FROM nested\_test2 ARRAY JOIN nest1 where nest1.x=3;

\--一个用户下挂了多个订单  
SELECT s, nest1.x, nest1.y,nest2.x2 ,nest2.y2  FROM nested\_test2 ARRAY JOIN nest1 where s='Hello';

\--对x值进行拆分  
SELECT s, nest1.x, nest1.y FROM nested\_test2 ARRAY JOIN nest1.x;

\--对x值进行拆分  
SELECT s, nest1.x, nest1.y FROM nested\_test2 ARRAY JOIN nest1.y;

\-- 对x，y进行拆分  
SELECT s, nest1.x, nest1.y FROM nested\_test2 ARRAY JOIN nest1.x, nest1.y;

\---- 对数组进行枚举  
SELECT s, num FROM nested\_test2  ARRAY JOIN arrayEnumerate(nest1.x) as num;

\--  
SELECT s, n.x, n.y, nest1.x, nest1.y,num FROM nested\_test2   
ARRAY JOIN nest1 AS n, arrayEnumerate(nest1.x) as num;

select order\_id,user\_id,user\_no from unity\_order where user\_no is null limit 10;

\--  临时表(TEMPORARY)

在所有情况下，如果指定临时表，就会创建临时表。临时表有以下特点：

临时表在会话结束时消失，包括连接丢失。  
一个临时表是用内存引擎创建的。其他表引擎不受支持。  
DB不能为临时表指定。它是在数据库之外创建的。  
如果临时表与另一个表的名称相同，并且查询指定表名而不指定DB，则使用临时表。  
对于分布式查询处理，查询中使用的临时表被传递给远程服务器。

CREATE TEMPORARY TABLE IF NOT EXISTS test.member\_new\_cp ENGINE = MergeTree   
AS SELECT \* from  test.member\_new;

\--success  
CREATE TEMPORARY TABLE IF NOT EXISTS test.member\_new\_cp ENGINE = Memory   
AS SELECT \* from  test.member\_new;

\-- 创建视图

CREATE VIEW member\_new\_view AS SELECT \* from test.member\_new;

CREATE VIEW member\_new\_view\_simple AS SELECT ID,user\_id,member\_grade from test.member\_new;

drop table member\_new\_orders\_view;  
CREATE VIEW member\_new\_orders\_view AS   
SELECT user\_id,count(order\_id) as cnt  
from  (  
SELECT user\_id,member\_grade from test.member\_new   
)any left join   
(select user\_no as user\_id,order\_id from test.unity\_order  
)using user\_id  
group by user\_id  
order by cnt desc;

\-- 全匹配数据 all left join

drop table member\_new\_orders\_view\_all;  
CREATE VIEW member\_new\_orders\_view\_all AS   
SELECT user\_id,count(order\_id) as cnt  
from  (  
SELECT user\_id,member\_grade from test.member\_new   
)all left join   
(select user\_no as user\_id,order\_id from test.unity\_order  
)using user\_id  
group by user\_id  
order by cnt desc;

\--查询操作符和子查询可以发生在查询的任何部分，包括聚合函数和lambda函数  
select id,create\_date,c2,(select max(AirlineID) from test.ontime\_wpp) as aa from test.test;

SELECT  
    EventDate,  
    avg(UserID IN  
    (  
        SELECT UserID  
        FROM test.hits  
        WHERE EventDate = toDate('2014-03-17')  
    )) AS ratio  
FROM test.hits  
GROUP BY EventDate  
ORDER BY EventDate Asc

select length(remark) from test.unity\_order limit 10;

select order\_id,length(remark) len,remark ,str(remark,0,3)  
from test.unity\_order where length(remark) >= 1 limit 100;

\--

create table t1 (id UInt16, name String) ENGINE=TinyLog;  
create table t2 (id UInt16, name String) ENGINE=TinyLog;  
create table t3 (id UInt16, name String) ENGINE=TinyLog;

insert into t1(id, name) values (1, 'first');  
insert into t1(id, name) values (12, 'first2');

insert into t1(id, name) values (13, 'first4');  
insert into t2(id, name) values (2, 'xxxx');  
insert into t3(id, name) values (12, 'i am in t3');

insert into t1(id, name) values (1, 'first');

insert into test\_df select 11, 'first2','2019-10-13';

CREATE TABLE default.test\_df (\`code\` String, \`description\` Nullable(String), \`ckbizdate\` Date) ENGINE = MergeTree(ckbizdate, code, 8192);

insert into test\_df select 132, 'first2','2019-10-20';

ssh dn2 'clickhouse-client --query="alter table  default.test\_df delete where ckbizdate =2019-10-20 " '

\--表进行合并查询，不支持更新操作  
create table t\_merge (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');

select  \* from t\_merge

select \_table,\* from t\_merge order by id desc

\---   
create table t\_repalce (gmt  Date, id UInt16, name String, point UInt16)   
ENGINE=ReplacingMergeTree(gmt, (name), 10, point);

insert into t\_repalce (gmt, id, name, point) values ('2017-07-10', 1, 'a', 20);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-10', 1, 'a', 30);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 1, 'a', 20);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 1, 'a', 30);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 1, 'a', 10);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 2, 'a', 10);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 3, 'b', 10);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 3, 'c', 10);  
insert into t\_repalce (gmt, id, name, point) values ('2017-07-11', 3, 'c', 20);

\--  多值判断  
SELECT   
    number,   
    caseWithoutExpression((number > 5) AND (number < 10), number + 1,  
    (number >= 10) AND (number < 15), number \* 2, 0)  
FROM system.numbers   
WHERE number < 20  
LIMIT 16;

\-- 行转列  
select user\_id,groupUniqArray(order\_id)   
from unity\_order   
group by user\_id   
having count(\*) >4 limit 10;

select user\_id,arrayMap(x-> toString(x), groupArray(order\_id))  
from unity\_order where order\_id>0 and user\_id != ''  group by user\_id  having count(\*) > 4 limit 10;

\-- json 格式化返回结果   
select user\_id,concat('\[',toString(groupUniqArray(order\_id)),'\]' ) json  
from unity\_order   
where user\_id != ''    
group by user\_id    
having count(\*) >4 limit 10;

参考技术  
https://www.zouyesheng.com/clickhouse.html\#toc8  
https://blog.csdn.net/vagabond6/article/details/79556968

https://zhuanlan.zhihu.com/p/22165241  
常用函数  
https://blog.csdn.net/vagabond6/article/details/79580371

有兴趣的可以加本人微信号:  wangpengpeng0525

码字不易，有闲钱可以打赏杯奶茶喝

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70]: /images/20230312/9d7090b851234b07a4fc8b622d845834.png