什么是物化视图
在数据库中视图指的是通过一张或者多张表查询出来的逻辑表,就类似网页版的拳皇一样按UIO就会放招而不是用上下上下BABA那样按,视图就是UIO记录了招式的过程但是并不记录数据.
而clickhouse的物化视图则是把查询的结果和相应的引擎存入到磁盘或内存中,类似形成了一张新的表,但是表中会对自己数据进行重新组织.
使用物化视图好处
可以避免对基础表的频繁查询并复用结果,从而显著提升查询的性能
基本语法
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT …
- 若使用POPULATE则会将历史记录数据全部导入进视图中,最好别用,想要将历史记录导入则使用insert插入数据就行
创建物化视图
创建源表
CREATE TABLE hits_test
(
EventDate Date,
CounterID UInt32,
UserID UInt64,
URL String,
Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192;
向表中插入数据
INSERT INTO hits_test
SELECT
EventDate,
CounterID,
UserID,
URL,
Income
FROM hits_v1
limit 10000;
创建物化视图
CREATE MATERIALIZED VIEW hits_mv
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID))
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2014-03-20'
GROUP BY UserID,EventDate;
框起来的为物化视图自己的数据规则
向物化视图中增加数据
INSERT INTO hits_test
SELECT
EventDate,
CounterID,
UserID,
URL,
Income
FROM hits_v1
WHERE EventDate >= '2014-03-23'
limit 10;
导入历史数据
最好别用POPULATE导入历史数据
将hits_test表中的数据导入物化视图中
INSERT INTO hits_mv
SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate;