clickhouse在用戶畫像中的使用
將用戶標(biāo)簽放在clickhouse中，并且用bitmap形式皿伺，可以減少用戶空間券册，同時(shí)能夠加快用戶查詢標(biāo)簽的效率，現(xiàn)在很多企業(yè)采用clickhouse + bitmap解決用戶畫像的問題
//1:建立bitmap的分布式表
CREATE TABLE test.bitmap_test
(
`name` String,
`value` String,
`bitmap` AggregateFunction(groupBitmap, UInt32),
`dt` Date
)
ENGINE = Distributed('test', 'test', 'test', rand())
//2:bitmap 查詢符合條件的數(shù)據(jù)集合bitmap
SELECT groupBitmapMergeState(uid_bitmap) FROM test.bitmap_test WHERE dt='2021-06-23'饥侵；
//3: 將得到的bitmap 中uid的集合，找出對(duì)應(yīng)的uid;
with(SELECT groupBitmapMergeState(uid_bitmap) FROM test.bitmap_test WHERE dt='2021-06-23') as temp SELECT arrayJoin(bitmapToArray(temp)
clickhouse 在地理位置服務(wù)中的使用
目前很多企業(yè)需要提供基于地理位置的服務(wù)衣屏，同時(shí)能夠計(jì)算出幾公里內(nèi)的一些
服務(wù)信息躏升；
查詢tb_distance 中距離坐標(biāo)位置為（x,y）小于5000米的所有記錄
select * from tb_distance where greatCircleDistance(x,y,lnt,lat)<5000
clickhouse常見的sql優(yōu)化
sql慢查大部分主要體現(xiàn)在cpu 負(fù)載過高，io過高狼忱，或者查詢的列中無索引導(dǎo)致的膨疏；注意；clickhouse本身不太支持高并發(fā)的場(chǎng)景钻弄，qps過高會(huì)導(dǎo)致clickhouse服務(wù)器cpu過高佃却，導(dǎo)致慢查
在這些情況下;常見的考慮的是 sql中是否有復(fù)雜的運(yùn)算，查詢的數(shù)量量是否過大窘俺，查詢的列中索引是否有效饲帅；
sql 查詢特點(diǎn)：數(shù)量大，且分區(qū)跨度大
data表格中有8億多條數(shù)據(jù)瘤泪，data表按照p_data_day 分區(qū)灶泵；
select sn,COUNT(1) as valueQt from data WHERE sn='70A0600018109' and p_day >= '2017-01-01' and p_data_day < '2020-08-13'
group by sn;
數(shù)據(jù)會(huì)遍歷整個(gè)分區(qū)，數(shù)據(jù)平均在1s左右分鐘返回 ;
優(yōu)化思路：減少不必要數(shù)據(jù)的遍歷（分區(qū)）对途；充分利用clickhouse 索引（group by 索引）
- 針對(duì)sn的查詢丘逸，建立物化視圖；將8億條數(shù)據(jù)按照sn號(hào)以及device_id（mac_code）建立256個(gè)分區(qū)掀宋；
create MATERIALIZED VIEW IF NOT EXISTS data_sn_materialized
engine = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data_sn_materialized', '{replica}')
PARTITION BY sn_sort_key ORDER BY (sn_sort_key,sn,p_day)
AS select halfMD5(_sn) % 256 as sn_sort_key,sn,p_day,count() as cnt
from data group by sn_sort_key,sn,p_day;
優(yōu)化后
查詢語句深纲；保持原來的出參和入?yún)⒉蛔冎俪瑪?shù)據(jù)能夠在200ms以內(nèi)返回，
sql 查詢特點(diǎn)：數(shù)量大湃鹊，且分區(qū)跨度大
data 表格數(shù)據(jù)量在10億多條儒喊，建表語句如下
CREATE TABLE data (
`data_day` Date,
`flow_type` UInt32 DEFAULT CAST(0,
'UInt32'),
.....
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data', '{replica}') PARTITION BY data_day ORDER BY (flow_type, data_day) SETTINGS index_granularity = 8192;
查詢語句
select ... from data where data_day = '2020-09-11'
我們觀察到查詢數(shù)據(jù)的時(shí)候，總是會(huì)具體到昨天币呵；而且歷史的數(shù)據(jù)不會(huì)再使用怀愧；
優(yōu)化思路： 使用clickhouse的TTL，減少表容量余赢，
CREATE TABLE dwrt.lc_order_flow (
`data_day` Date,
.....
`flow_type` UInt32 DEFAULT CAST(0,
'UInt32'),
....
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data', '{replica}') PARTITION BY data_day ORDER BY (data_day, flow_type) TTL data_day + toIntervalDay(7) SETTINGS index_granularity = 8192;
