我的开源
ClickHouse中物化视图初体验
背景
什么是物化视图？略
优点
- 数据可以单独存储，不受上游表TTL限制。
- 查询更快
原理
图转自[1]
执行完上述CREATE MATERIALIZED VIEW语句后，通过SHOW TABLES语句查询，会发现有一张名为.inner.[物化视图名]的表，这就是持久化物化视图数据的表。
方法
查询数据
SELECT
time,
timestamp,
date,
hour,
minute,
duid,
record_id,
name as asr_provider,
log_map_value[indexOf(log_map_key, 'dialect_language')] as dialect_language,
response,
log_map_value[indexOf(log_map_key, 'client_ip')] as client_ip,
toFloat32(log_map_value[indexOf(log_map_key, 'audio_duration')]) AS audio_duration_in_ms
FROM
note.bot_ods_server_log_local
WHERE
(date == '2022-03-08')
AND event_id = 'bot_gateway_asr_process'
AND length(response) >= 6
AND length(duid) > 4
AND ((log_map_value[indexOf(log_map_key, 'dialect_language')]) = 'cantonese')
AND (length(log_map_value[indexOf(log_map_key, 'audio_duration')]) > 0)
LIMIT 100
建立视图
CREATE MATERIALIZED VIEW IF NOT EXISTS note.ods_mview_language_asr_cantonese_local
ON CLUSTER 'default'
ENGINE = ReplacingMergeTree()
PARTITION BY date
ORDER BY (duid, record_id, timestamp)
TTL toDate(date) + toIntervalDay(30)
SETTINGS index_granularity = 8192
AS
SELECT
time,
timestamp,
date,
hour,
minute,
duid,
record_id,
name as asr_provider,
log_map_value[indexOf(log_map_key, 'dialect_language')] as dialect_language,
response,
log_map_value[indexOf(log_map_key, 'client_ip')] as client_ip,
toFloat32(log_map_value[indexOf(log_map_key, 'audio_duration')]) AS audio_duration_in_ms
FROM
note.bot_ods_server_log_local
WHERE
event_id = 'bot_gateway_asr_process'
AND length(response) >= 2
AND length(duid) > 4
AND ((log_map_value[indexOf(log_map_key, 'dialect_language')]) = 'cantonese')
AND (length(log_map_value[indexOf(log_map_key, 'audio_duration')]) > 0)
建立分布式表
CREATE TABLE note.ods_mview_language_asr_cantonese
ON CLUSTER default
as note.ods_mview_language_asr_cantonese_local
ENGINE = Distributed(default, note, ods_mview_language_asr_cantonese_local, rand());