clickhouse物化视图实时构建宽表

最新推荐文章于 2024-11-21 18:18:30 发布

猿憨憨

最新推荐文章于 2024-11-21 18:18:30 发布

阅读量2k

点赞数

分类专栏：大数据

本文链接：https://blog.youkuaiyun.com/a1265262132/article/details/118406483

版权

大数据专栏收录该内容

5 篇文章

订阅专栏

该博客介绍了如何创建一个Materialized View `dws_iot.realtime_device`，使用CollapsingMergeTree引擎并根据created_date进行分区，用于聚合设备的实时数据。通过LEFT JOIN操作连接了building、floor和room的子集，只包含有正标志的记录，确保数据的准确性。此视图优化了数据查询效率，实现了主表和宽表的动态更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--实测只有主表动宽表动，从表动主表不动宽表不动
CREATE MATERIALIZED VIEW dws_iot.realtime_device
engine = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(created_date)
order by id
POPULATE AS 
SELECT
    dv.id AS id,
    dv.sign AS sign,
    dv.created_date AS created_date,
    dv.name AS dv_name,
    dv.business_id AS dv_business_id,
    dv.identity AS dv_identity,
    dv.installer_id AS dv_installer,
    dv.latitude AS dv_latitude,
    dv.longitude AS dv_longitude,
    dv.status AS dv_status,
    dv.building_id AS dv_building_id,
    dv.floor_id AS dv_floor_id,
    dv.room_id AS dv_room,
    bl.name AS bl_buildName,
    fl.name AS fl_floorName,
    rm.name AS rm_roomName
FROM dwd_iot.device AS dv
LEFT JOIN 
(
    SELECT
        id,
        name
    FROM dwd_iot.building
    GROUP BY
        id,
        name
    HAVING sum(sign) > 0
) AS bl ON dv.building_id = bl.id
LEFT JOIN 
(
    SELECT
        id,
        name
    FROM dwd_iot.floor
    GROUP BY
        id,
        name
    HAVING sum(sign) > 0
) AS fl ON dv.floor_id = fl.id
LEFT JOIN 
(
    SELECT
        id,
        name
    FROM dwd_iot.room
    GROUP BY
        id,
        name
    HAVING sum(sign) > 0
) AS rm ON dv.room_id = rm.id