motor de tabla SummingMergeTree

El motor hereda de MergeTree. La diferencia es que, al fusionar partes de datos en tablas SummingMergeTree, ClickHouse sustituye todas las filas con la misma clave primaria (o, más exactamente, con la misma clave de ordenación) por una sola fila que contiene los valores sumados de las columnas con tipo de dato numérico. Si la clave de ordenación está compuesta de tal manera que un único valor de clave corresponde a un gran número de filas, esto reduce significativamente el volumen de almacenamiento y acelera la consulta de datos.

Recomendamos usar este motor junto con MergeTree. Almacene los datos completos en una tabla MergeTree y use SummingMergeTree para almacenar datos agregados, por ejemplo, al preparar informes. Este enfoque evitará la pérdida de datos valiosos debido a una clave primaria mal definida.

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

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

columns - una tupla con los nombres de las columnas cuyos valores se sumarán. Parámetro opcional. Las columnas deben ser de tipo numérico y no deben formar parte de la partición ni de la clave de ordenación.

Si no se especifica columns, ClickHouse suma los valores de todas las columnas de tipo de datos numérico que no formen parte de la clave de ordenación.

Al crear una tabla SummingMergeTree, 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 [=] SummingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, [columns])

Considere la siguiente tabla:

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

Inserta datos en ella:

INSERT INTO summtt VALUES(1,1),(1,2),(2,1)

ClickHouse puede no sumar completamente todas las filas (ver abajo), por lo que usamos la función de agregación sum y la cláusula GROUP BY en la consulta.

SELECT key, sum(value) FROM summtt GROUP BY key

┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

Cuando los datos se insertan en una tabla, se guardan tal cual. ClickHouse fusiona periódicamente las partes de datos insertadas, y es entonces cuando las filas con la misma clave primaria se suman y se sustituyen por una sola fila en cada parte de datos resultante.

ClickHouse puede fusionar las partes de datos de modo que distintas partes de datos resultantes puedan contener filas con la misma clave primaria; es decir, la suma será incompleta. Por lo tanto, en una consulta (SELECT) se deben usar una función de agregación sum() y la cláusula GROUP BY, como se describe en el ejemplo anterior.

Se suman los valores de las columnas con tipo de dato numérico. El conjunto de columnas se define mediante el parámetro columns.

Si los valores eran 0 en todas las columnas que se suman, la fila se elimina.

Si una columna no forma parte de la clave primaria y no se suma, se selecciona un valor arbitrario de los existentes.

Los valores no se suman en las columnas de la clave primaria.

En las columnas de tipo AggregateFunction, ClickHouse se comporta como el motor AggregatingMergeTree, agregando de acuerdo con la función.

Una tabla puede tener estructuras de datos anidadas que se procesan de forma especial.

Si el nombre de una tabla anidada termina en Map y contiene al menos dos columnas que cumplen los siguientes criterios:

• la primera columna es numérica (*Int*, Date, DateTime) o de cadena (String, FixedString), llamémosla key,
• las demás columnas son aritméticas (*Int*, Float32/64), llamémoslas (values...),

entonces esta tabla anidada se interpreta como una correspondencia de key => (values...), y al fusionar sus filas, los elementos de ambos conjuntos de datos se combinan por key, sumando los (values...) correspondientes.

Ejemplos:

DROP TABLE IF EXISTS nested_sum;
CREATE TABLE nested_sum
(
    date Date,
    site UInt32,
    hitsMap Nested(
        browser String,
        imps UInt32,
        clicks UInt32
    )
) ENGINE = SummingMergeTree
PRIMARY KEY (date, site);

INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Firefox', 'Opera'], [10, 5], [2, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Chrome', 'Firefox'], [20, 1], [1, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['IE'], [22], [0]);
INSERT INTO nested_sum VALUES ('2020-01-01', 10, ['Chrome'], [4], [3]);

OPTIMIZE TABLE nested_sum FINAL; -- emulate merge

SELECT * FROM nested_sum;
┌───────date─┬─site─┬─hitsMap.browser───────────────────┬─hitsMap.imps─┬─hitsMap.clicks─┐
│ 2020-01-01 │   10 │ ['Chrome']                        │ [4]          │ [3]            │
│ 2020-01-01 │   12 │ ['Chrome','Firefox','IE','Opera'] │ [20,11,22,5] │ [1,3,0,1]      │
└────────────┴──────┴───────────────────────────────────┴──────────────┴────────────────┘

SELECT
    site,
    browser,
    impressions,
    clicks
FROM
(
    SELECT
        site,
        sumMap(hitsMap.browser, hitsMap.imps, hitsMap.clicks) AS imps_map
    FROM nested_sum
    GROUP BY site
)
ARRAY JOIN
    imps_map.1 AS browser,
    imps_map.2 AS impressions,
    imps_map.3 AS clicks;

┌─site─┬─browser─┬─impressions─┬─clicks─┐
│   12 │ Chrome  │          20 │      1 │
│   12 │ Firefox │          11 │      3 │
│   12 │ IE      │          22 │      0 │
│   12 │ Opera   │           5 │      1 │
│   10 │ Chrome  │           4 │      3 │
└──────┴─────────┴─────────────┴────────┘

Al solicitar datos, utilice la función sumMap(key, value) para agregar Map.

En una estructura de datos anidada, no es necesario especificar sus columnas en la tupla de columnas para la suma.

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 suma de forma independiente. Esto le permite almacenar varias métricas en una sola columna Tuple y hacer que se sumen elemento a elemento durante las fusiones.

A las subcolumnas aplanadas se les aplican las mismas reglas que a las columnas normales:

• Solo se suman las subcolumnas numéricas.
• Las subcolumnas que pertenecen a un Tuple en la clave de ordenación o en la clave de partición se excluyen de la suma.
• Si se especifica columns, solo se suman las subcolumnas de las columnas Tuple incluidas en la lista.
• Si todas las subcolumnas numéricas de una fila son cero después de la suma, la fila se elimina.

CREATE TABLE summing_tuples
(
    key UInt32,
    metrics Tuple(
        impressions UInt64,
        clicks UInt64,
        nested Tuple(
            conversions UInt64
        )
    )
) ENGINE = SummingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO summing_tuples VALUES (1, (100, 10, (1)));
INSERT INTO summing_tuples VALUES (1, (200, 20, (3)));

OPTIMIZE TABLE summing_tuples FINAL;

SELECT key, metrics.impressions, metrics.clicks, metrics.nested.conversions FROM summing_tuples;

┌─key─┬─metrics.impressions─┬─metrics.clicks─┬─metrics.nested.conversions─┐
│   1 │                 300 │             30 │                          4 │
└─────┴─────────────────────┴────────────────┴────────────────────────────┘

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