ClickHou复制表+物化视图样例
create databa if not EXISTS test on cluster vccluster
;
create st_a1 on cluster vccluster (
datatime DateTime('Asia/Shanghai')DEFAULT CAST('1990-01-01 00:00:00','DateTime(\'Asia/Shanghai\')')COMMENT'数据时间' ,`dt` Int64 MATERIALIZED toYYYYMMDD(datatime)COMMENT'数据⽇期'
,`version` String DEFAULT''COMMENT'版本'
,`uuid` String DEFAULT''COMMENT'uuid'
,`drtime` Int64 DEFAULT0COMMENT'持续时间'
)
ENGINE= ReplicatedMergeTree(
'/clickhou/tables/{shard}/st_a1',
'{replica}'
)
什么是浮游生物PARTITION BY toYYYYMMDD(datatime)
PRIMARY KEY(dt*-1,version,uuid)
ORDER BY
(
dt*-1
,version
,uuid
) TTL datatime + toIntervalDay(7)
SETTINGS index_granularity =8192
;
create databa if not EXISTS test_olap on cluster vccluster
;
平常人生CREATE TABLE IF NOT EXISTS st_a1
ON CLUSTER vccluster
st_a1
ENGINE=Distributed(vccluster,test,test_a1,rand());
;
create table if not EXISTS test.mvt_test_a1 ON CLUSTER vccluster
(
`dt` Int64 COMMENT'数据⽇期'
,`day`DateTime('Asia/Shanghai')
,`hour`DateTime('Asia/Shanghai')
,`tenm`DateTime('Asia/Shanghai')
,`version` String DEFAULT''COMMENT'版本'
,`uv` AggregateFunction(uniqCombined, String)COMMENT'uv,⾮精确去重，误差在千分之⼀以下'
,`pv` AggregateFunction(sum, UInt8)COMMENT'pv'
,`drtime_sum` AggregateFunction(sum, Int64)COMMENT'drtime_sum'
)
ENGINE= ReplicatedAggregatingMergeTree(
'/clickhou/tables/{shard}/test.mvt_test_a1',
'{replica}'
)PARTITION BY toYYYYMMDD(day)
ORDER BY
(
dt*-1
,day
,hour
,tenm
,version
)
TTL day+ toIntervalDay(62)
SETTINGS index_granularity =8192
;
CREATE TABLE IF NOT EXISTS test_olap.mvt_test_a1
ON CLUSTER vccluster
AS test.mvt_test_a1
ENGINE=Distributed(vccluster,test,mvt_test_a1,rand());
;
CREATE MATERIALIZED VIEW test.shard_mv_test_a1 on cluster vccluster TO test.mvt_test_a1 (
`dt` Int64 COMMENT'数据⽇期'
`dt` Int64 COMMENT'数据⽇期'
,`day`DateTime('Asia/Shanghai')
,
实践能力怎么写
`hour`DateTime('Asia/Shanghai')
,`tenm`DateTime('Asia/Shanghai')
,`version` String DEFAULT''COMMENT'版本'
,`uv` AggregateFunction(uniqCombined, String)COMMENT'uv,⾮精确去重，误差在千分之⼀以下'
炸馒头片
,`pv` AggregateFunction(sum, UInt8)COMMENT'pv'
,`drtime_sum` AggregateFunction(sum, Int64)COMMENT'drtime_sum'
)
as
SELECT
dt
,toStartOfDay(datatime)AS day
,
toStartOfHour(datatime)AS hour
,toStartOfTenMinutes(datatime)AS tenm
,version
,uniqCombinedState(uuid)AS uv
,sumState(1)AS pv
,sumState(drtime)as drtime_sum
美在路上
st_a1
group by dt
,day
,hour
,tenm
,
version
;
inrt into st_a1 (datatime,version,uuid,drtime)VALUES
('2020-12-29 20:00:00','1.1','96b32b57-3af9-431f-b80b-2f79457061f9',10)
眼科近视治疗,('2020-12-29 20:00:10','1.1','ce7b7297-96b2-46da-8cbc-d6895d6b4d99',20)
,('2020-12-29 20:20:00','1.1','9ba3df40-5b51-47dc-8474-3919db92fde6',20)
,('2020-12-29 20:20:20','1.1','ce7b7297-96b2-46da-8cbc-d6895d6b4d99',50)
,('2020-12-29 21:00:00','1.1','ce7b7297-96b2-46da-8cbc-d6895d6b4d99',90)
,('2020-12-29 21:30:00','1.1','d0dec466-f292-4782-bf4d-4aa5426b4081',100)
;
SELECT*st_a1;
-
- ┌────────────datatime─┬─version─┬─uuid─────────────────────────────────┬─drtime─┐
-- │ 2020-12-29 20:00:00 │ 1.1 │ 96b32b57-3af9-431f-b80b-2f79457061f9 │ 10 │
-- │ 2020-12-29 20:20:00 │ 1.1 │ 9ba3df40-5b51-47dc-8474-3919db92fde6 │ 20 │
明查暗访-- │ 2020-12-29 20:00:10 │ 1.1 │ ce7b7297-96b2-46da-8cbc-d6895d6b4d99 │ 20 │
-- │ 2020-12-29 20:20:20 │ 1.1 │ ce7b7297-96b2-46da-8cbc-d6895d6b4d99 │ 50 │
-- │ 2020-12-29 21:00:00 │ 1.1 │ ce7b7297-96b2-46da-8cbc-d6895d6b4d99 │ 90 │
-- │ 2020-12-29 21:30:00 │ 1.1 │ d0dec466-f292-4782-bf4d-4aa5426b4081 │ 100 │
-- └─────────────────────┴─────────┴──────────────────────────────────────┴────────┘
-- 6 rows in t. Elapd: 0.011 c.
SELECT*from test_olap.mvt_test_a1
我爱广州-- ┌───────dt─┬─────────────────day─┬────────────────hour─┬────────────────tenm─┬─version─┬─uv───────┬─pv─┬─drtime_sum─┐
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 20:00:00 │ 2020-12-29 20:00:00 │ 1.1 │ !��p�l��,�&ruf�│││
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 20:00:00 │ 2020-12-29 20:20:00 │ 1.1 │�E���
-- =��,�&ruf�││ F │
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 21:00:00 │ 2020-12-29 21:00:00 │ 1.1 │�,�&ruf�││ Z │
-- �g ││ d │0:00 │ 2020-12-29 21:00:00 │ 2020-12-29 21:30:00 │ 1.1 │ li�
-- └──────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────┴──────────┴────┴────────────┘
-- 4 rows in t. Elapd: 0.012 c.
SELECT dt
,day
,hour
,tenm
,version
,uniqCombinedMerge(uv)as uv
,sumMerge(pv)as pv
,sumMerge(drtime_sum)as drtime_sum
,sumMerge(drtime_sum)as drtime_sum
from test_olap.mvt_test_a1
group by dt
,
day
,hour
,tenm
,version
-- ┌───────dt─┬─────────────────day─┬────────────────hour─┬────────────────tenm─┬─version─┬─uv─┬─pv─┬─drtime_sum─┐
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 21:00:00 │ 2020-12-29 21:30:00 │ 1.1 │ 1 │ 1 │ 100 │
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 20:00:00 │ 2020-12-29 20:00:00 │ 1.1 │ 2 │ 2 │ 30 │
-- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 21:00:00 │ 2020-12-29 21:00:00 │ 1.1 │ 1 │ 1 │ 90 │
-
- │ 20201229 │ 2020-12-29 00:00:00 │ 2020-12-29 20:00:00 │ 2020-12-29 20:20:00 │ 1.1 │ 2 │ 2 │ 70 │
-- └──────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────┴────┴────┴────────────┘SELECT dt
,uniqCombinedMerge(uv)as uv
,sumMerge(pv)as pv
,sumMerge(drtime_sum)as drtime_sum
from test_olap.mvt_test_a1
group by dt
-- ┌───────dt─┬─uv─┬─pv─┬─drtime_sum─┐
-- │ 20201229 │ 4 │ 6 │ 290 │
-
- └──────────┴────┴────┴────────────┘
