Lab AggStates

Agregados en ClickHouse

El objetivo de este laboratorio es mostrar la conjunción de mecanismos de ClickHouse, fundamentalmente la familia de motores MergeTree, los estados de agregación y las vistas materializadas que explican el rendimiento alcanzado por este gestor en el procesamiento de las consultas de agregados

DDL¶

-- DDL tabla
DROP TABLE IF EXISTS etsiinf.Events;
CREATE TABLE etsiinf.Events (
  EventDate DateTime('Europe/Madrid') NOT NULL,
  EventID UInt8 NOT NULL,
  V1 UInt32 NOT NULL,
  V2 UInt32 NOT NULL
)
ENGINE = MergeTree ORDER BY (EventDate, EventID);

DML¶

SELECT toStartOfHour(EventDate), EventDate, EventID, V1, V2 FROM etsiinf.Events;

-- 1ª tanda
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 01:03:00', 'Europe/Madrid'), 1, 1, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 01:02:00', 'Europe/Madrid'), 1, 2, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 02:02:00', 'Europe/Madrid'), 2, 3, 2);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 01:03:00', 'Europe/Madrid'), 1, 3, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 02:04:00', 'Europe/Madrid'), 2, 2, 4);

-- 2ª tanda
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 03:03:00', 'Europe/Madrid'), 1, 1, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 03:02:00', 'Europe/Madrid'), 1, 2, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 03:02:00', 'Europe/Madrid'), 2, 3, 2);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 03:03:00', 'Europe/Madrid'), 1, 3, 3);
INSERT INTO etsiinf.Events VALUES (toDateTime('2025-04-28 03:04:00', 'Europe/Madrid'), 2, 2, 4);

AggregatingMergeTree¶

Versión con vista materializada

En este caso se crea una vista materializada denominada Events_mv sobre la tabla Events
En la vista se definen tres columnas sV1, mV2 y uV2 que son combinadores -State con tipo AggregateFunction. Por consiguiente, no es posible consultar estas columnas directamente pues son estados intermedios de agregación. En la consulta debe invocarse el combinador -Merge correspondiente

DDL: Vista AggregatingMergeTree¶

-- DDL vista
DROP VIEW etsiinf.Events_mv;
CREATE MATERIALIZED VIEW etsiinf.Events_mv
(
    EventDate DateTime('Europe/Madrid') NOT NULL,
    EventID UInt8,
    sV1 AggregateFunction(sum, UInt32),  -- suma de V1s
    mV2 AggregateFunction(avg, UInt32),  -- media de V2s
    uV2 AggregateFunction(uniq, UInt32)  -- V2s diferentes
)
ENGINE = AggregatingMergeTree() ORDER BY (EventDate, EventID)
AS SELECT
       toStartOfHour(EventDate) AS EventDate,
       EventID,
       sumState(V1) AS sV1,
       avgState(V2) AS mV2,
       uniqState(V2) AS uV2
   FROM etsiinf.Events
   GROUP BY EventDate, EventID;
   ```

#### DML: consulta sobre la vista
```sql
-- OJO: combinadores -Merge
SELECT
    toString(EventDate) AS Fecha,
    EventID AS EvID,
    sumMerge(sV1) AS sV1,
    avgMerge(mV2) AS mV2,
    uniqMerge(uV2) AS uV2
FROM etsiinf.Events_mv
GROUP BY EventDate, EventID
ORDER BY EventDate, EventID;

Resultado

Tanda	Tabla Events	Vista Events_mv
1
2

Versión con tabla

En este segundo ejemplo se crea una tabla denominada aggEvents con motor AggregatingMergeTree y las mismas tres columnas sV1, mV2 y uV2 del ejemplo anterior. En este caso, la vista actualiza directamente sobre la tabla (claúsula TO)

DDL: tabla AggregatingMergeTree para almacenar los resultados de la vista¶

DROP TABLE IF EXISTS etsiinf.aggEvents;
CREATE TABLE etsiinf.aggEvents
(
    EventDate DateTime('Europe/Madrid') NOT NULL,
    EventID UInt8,
    sV1 AggregateFunction(sum, UInt32),  -- suma de V1s
    mV2 AggregateFunction(avg, UInt32),  -- media de V2s
    uV2 AggregateFunction(uniq, UInt32)  -- V2s diferentes
)
ENGINE = AggregatingMergeTree() ORDER BY (EventDate, EventID);

DDL: vista para los estados de las funciones de agregación¶

DROP VIEW etsiinf.aggEvents_mv;
CREATE MATERIALIZED VIEW etsiinf.aggEvents_mv
TO etsiinf.aggEvents
AS SELECT
       toStartOfHour(EventDate) AS EventDate,
       EventID,
       sumState(V1) AS sV1,
       avgState(V2) AS mV2,
       uniqState(V2) AS uV2
   FROM etsiinf.Events
   GROUP BY EventDate, EventID;

DML: consulta directamente sobre la tabla¶

SELECT
    toString(EventDate) AS Fecha,
    EventID AS EvID,
    sumMerge(sV1) AS sV1,
    avgMerge(mV2) AS mV2,
    uniqMerge(uV2) AS uV2
FROM etsiinf.aggEvents
GROUP BY EventDate, EventID
ORDER BY EventDate, EventID;

Resultado

Logicamente, es exactamente el mismo que en el caso anterior 👁️

Ejercicio¶

Automatización de acumulados y borrados

Extrapolar el planteamiento del ejercicio anterior a los casos de uso automatización de acumulados (SummingMergeTree) y/o eliminación de nulos o duplicados (ReplacingMergeTree, CoalescingMergeTree, CollapsingMergeTree)