motor de tabla de AggregatingMergeTree

Este motor hereda de MergeTree y modifica la lógica de fusión de las partes de datos. ClickHouse reemplaza todas las filas que tienen la misma clave primaria (o, más exactamente, la misma clave de ordenación) por una sola fila (dentro de una única parte de datos) que almacena una combinación de estados de funciones de agregación.

Puede usar tablas AggregatingMergeTree para la agregación incremental de datos, incluidas las vistas materializadas agregadas.

A continuación, puede ver un ejemplo de cómo usar AggregatingMergeTree y las funciones de agregación en el siguiente video:

Este motor procesa todas las columnas con los siguientes tipos:

• AggregateFunction
• SimpleAggregateFunction

Conviene usar AggregatingMergeTree si reduce el número de filas en varios órdenes de magnitud.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

Para ver una descripción de los parámetros de la solicitud, consulte la descripción de la solicitud.

Cláusulas de la consulta

Al crear una tabla AggregatingMergeTree, se requieren las mismas cláusulas que al crear una tabla MergeTree.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE [=] AggregatingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

Para insertar datos, use la consulta INSERT SELECT con funciones de agregación -State-. Al seleccionar datos de la tabla AggregatingMergeTree, use la cláusula GROUP BY y las mismas funciones de agregación que al insertar datos, pero usando el sufijo -Merge.

En los resultados de la consulta SELECT, los valores del tipo AggregateFunction tienen una representación binaria específica de la implementación en todos los formatos de salida de ClickHouse. Por ejemplo, si vuelca datos al formato TabSeparated con una consulta SELECT, este volcado puede volver a cargarse usando una consulta INSERT.

El siguiente ejemplo asume que tienes una base de datos llamada test. Si aún no existe, créala con el siguiente comando:

CREATE DATABASE test;

Ahora crea la tabla test.visits que contiene los datos sin procesar:

CREATE TABLE test.visits
 (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Sign Nullable(Int32),
    UserID Nullable(Int32)
) ENGINE = MergeTree ORDER BY (StartDate, CounterID);

A continuación, necesitas una tabla AggregatingMergeTree que almacene AggregationFunctions para llevar el registro del número total de visitas y del número de usuarios únicos.

Crea una vista materializada AggregatingMergeTree que monitorice la tabla test.visits y utilice el tipo AggregateFunction:

CREATE TABLE test.agg_visits (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Visits AggregateFunction(sum, Nullable(Int32)),
    Users AggregateFunction(uniq, Nullable(Int32))
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);

Cree una vista materializada que llene test.agg_visits a partir de test.visits:

CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits
AS SELECT
    StartDate,
    CounterID,
    sumState(Sign) AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY StartDate, CounterID;

Inserte datos en la tabla test.visits:

INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);

Los datos se insertan tanto en test.visits como en test.agg_visits.

Para obtener los datos agregados, ejecute una consulta como SELECT ... GROUP BY ... desde la vista materializada test.visits_mv:

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.visits_mv
GROUP BY StartDate
ORDER BY StartDate;

┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 2022-11-03 03:27:11.000 │      9 │     2 │
└─────────────────────────┴────────┴───────┘

Añade un par de registros más a test.visits, pero esta vez utiliza un timestamp diferente para uno de los registros:

INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5);

Ejecuta de nuevo la consulta SELECT, que devolverá el siguiente resultado:

┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 2022-11-03 03:27:11.000 │     16 │     3 │
│ 2022-11-26 07:00:31.000 │      5 │     1 │
└─────────────────────────┴────────┴───────┘

En algunos casos, puede que desee evitar la pre-agregación de filas en el momento de la inserción para trasladar el costo de la agregación del momento de inserción al momento de la fusión. Normalmente, es necesario incluir las columnas que no forman parte de la agregación en la cláusula GROUP BY de la definición de la vista materializada para evitar un error. Sin embargo, puede utilizar la función initializeAggregation con el parámetro optimize_on_insert = 0 (activado por defecto) para lograr esto. En este caso, ya no es necesario usar GROUP BY:

CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits
AS SELECT
    StartDate,
    CounterID,
    initializeAggregation('sumState', Sign) AS Visits,
    initializeAggregation('uniqState', UserID) AS Users
FROM test.visits;

Cuando la configuración allow_tuple_element_aggregation está habilitada, las columnas Tuple se aplanan de forma recursiva para que cada elemento hoja participe en la agregación de manera independiente. Esto significa que las subcolumnas AggregateFunction o SimpleAggregateFunction dentro de un Tuple se agregan según sus respectivas funciones, como si fueran columnas de nivel superior.

Las subcolumnas que pertenecen a un Tuple en la clave de ordenación se excluyen de la agregación. Las subcolumnas no agregadas se tratan como columnas ordinarias (se conserva el primer valor).

CREATE TABLE agg_tuples
(
    key UInt32,
    metrics Tuple(
        total_visits SimpleAggregateFunction(sum, UInt64),
        unique_users SimpleAggregateFunction(max, UInt64)
    )
) ENGINE = AggregatingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO agg_tuples VALUES (1, (100, 5));
INSERT INTO agg_tuples VALUES (1, (200, 8));
INSERT INTO agg_tuples VALUES (2, (50, 3));

OPTIMIZE TABLE agg_tuples FINAL;

SELECT key, metrics.total_visits, metrics.unique_users FROM agg_tuples ORDER BY key;

┌─key─┬─metrics.total_visits─┬─metrics.unique_users─┐
│   1 │                  300 │                    8 │
│   2 │                   50 │                    3 │
└─────┴──────────────────────┴──────────────────────┘

total_visits se agrega mediante sum (100 + 200 = 300), mientras que unique_users se agrega mediante max (max(5, 8) = 8).

• Blog: Uso de combinadores de agregación en ClickHouse