ApsaraDB for ClickHouse:Motores de tablas

MergeTree

MergeTree es el motor base para cargas de trabajo de inserción con alta demanda. Los datos se insertan en partes y se fusionan en segundo plano según el orden de clasificación definido por ORDER BY.

Ejemplo: MergeTree con particionamiento

1. Cree una tabla particionada por create_time y ordenada por (id, create_time).

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Inserte filas con claves primarias duplicadas.

   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);

3. Consulte la tabla.

   SELECT * FROM test_tbl;

   Resultado: se devuelven las cinco filas, incluidos los duplicados:

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Fuerce la compactación.

   OPTIMIZE TABLE test_tbl FINAL;

5. Vuelva a consultar.

   SELECT * FROM test_tbl;

   Resultado: los duplicados siguen presentes. MergeTree no los elimina.

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

Para obtener más información, consulte MergeTree.

ReplacingMergeTree

ReplacingMergeTree elimina filas duplicadas con la misma clave primaria durante la compactación en segundo plano. Utilícelo cuando sea aceptable una deduplicación eventual, ya que no garantiza unicidad al momento de la consulta.

Limitaciones

• En un clúster distribuido, las filas con la misma clave primaria pueden ubicarse en diferentes shards. La deduplicación solo ocurre dentro de un solo shard, no entre varios shards.

• Antes de que finalice la compactación, algunos duplicados pueden seguir presentes. La compactación se ejecuta en segundo plano con una programación impredecible.

• En conjuntos de datos grandes, ejecutar manualmente OPTIMIZE ... FINAL puede tardar mucho tiempo, lo que hace impráctica la deduplicación en tiempo real.

Ejemplo: Deduplicación con ReplacingMergeTree

1. Cree una tabla.

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Inserte filas con claves primarias duplicadas.

   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);

3. Consulte antes de la compactación: los duplicados siguen presentes.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Fuerce la compactación.

   OPTIMIZE TABLE test_tbl_replacing FINAL;

5. Vuelva a consultar: los duplicados se eliminaron.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

CollapsingMergeTree

CollapsingMergeTree rastrea los cambios de estado de las filas mediante una columna Sign en lugar de actualizar las filas en su lugar, ya que las actualizaciones son costosas en el modelo de almacenamiento de solo anexión de ClickHouse. Cada fila se marca como fila de estado (Sign = 1) o fila de cancelación (Sign = -1). Durante la compactación, los pares de filas de estado y de cancelación con la misma clave primaria se colapsan y se eliminan.

Cómo funciona el colapso

Para actualizar el estado de una fila:

1. Inserte una fila de cancelación con Sign = -1 y los mismos valores de clave primaria que la fila que desea reemplazar.

2. Inserte una nueva fila de estado con Sign = 1 y los valores actualizados.

Para eliminar el estado de una fila: inserte una fila de cancelación que coincida con la fila de estado original en todas las columnas excepto en Sign.

Notas de uso

• Si las filas de estado y de cancelación se insertan fuera de orden —por ejemplo, si se inserta una fila de cancelación y luego su fila de estado correspondiente llega por separado— es posible que no se colapsen correctamente. Considere usar VersionedCollapsingMergeTree para manejar inserciones fuera de orden.

• El colapso solo ocurre dentro de un solo shard. En un clúster distribuido, las filas con la misma clave primaria en diferentes nodos no se colapsan.

• Antes de que finalice la compactación, coexisten las filas de estado y de cancelación. Al ejecutar consultas agregadas antes de la compactación, ajuste su SQL:

  • Reemplace COUNT() por SUM(Sign)

  • Reemplace SUM(col) por SUM(col * Sign)

Ejemplo: Actualizaciones de estado con CollapsingMergeTree

1. Cree una tabla con una columna Sign.

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Inserte la fila de estado inicial.

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

3. Actualice el estado: inserte una fila de cancelación para el estado anterior y una nueva fila de estado.

   Nota

   Inserte la fila de cancelación y la nueva fila de estado en el mismo lote. Si inserta la fila de cancelación y luego la fila de estado en operaciones separadas, podrían llegar fuera de orden. En ese caso, incluso si se fuerza la compactación, las filas con la misma clave primaria no podrán colapsarse ni eliminarse.

   INSERT INTO test_tbl_collapsing VALUES
     (4324182021466249494, 5, 146, -1),  -- cancel old state
     (4324182021466249494, 6, 185, 1);   -- new state

4. Consulte antes de la compactación: las tres filas están presentes.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    5      │    146   │   1   │
   │ 4324182021466249494 │    5      │    146   │  -1   │
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

   Para obtener resultados agregados correctos antes de la compactación, use expresiones que consideren Sign:

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration──┐
   │ 4324182021466249494 │    6      │    185    │
   └─────────────────────┴───────────┴───────────┘

5. Fuerce la compactación.

   OPTIMIZE TABLE test_tbl_collapsing FINAL;

6. Vuelva a consultar: solo permanece la última fila de estado.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree extiende CollapsingMergeTree con una columna Version que permite al motor emparejar correctamente las filas de estado y de cancelación sin importar el orden de inserción. Durante la compactación, se colapsan y eliminan las filas que tienen la misma clave primaria, el mismo valor de Version y valores opuestos de Sign.

Utilice este motor cuando las filas puedan llegar fuera de orden; por ejemplo, en pipelines basados en eventos donde los eventos de cancelación pueden procesarse antes que los eventos de estado a los que hacen referencia.

Al igual que con CollapsingMergeTree, ajuste las consultas agregadas para usar SUM(Sign) en lugar de COUNT() y SUM(col * Sign) en lugar de SUM(col).

Ejemplo: VersionedCollapsingMergeTree con inserciones fuera de orden

1. Cree una tabla con columnas Sign y Version.

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. Inserte primero una fila de cancelación (fuera de orden).

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. Inserte la fila de estado correspondiente y una nueva fila de estado.

   INSERT INTO test_tbl_Versioned VALUES
     (4324182021466249494, 5, 146, 1, 1),   -- state row matching the cancel above
     (4324182021466249494, 6, 185, 1, 2);   -- new state

4. Consulte antes de la compactación.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

   Use agregaciones que consideren Sign para obtener resultados correctos antes de la compactación:

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration─┐
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

5. Fuerce la compactación.

   OPTIMIZE TABLE test_tbl_Versioned FINAL;

6. Vuelva a consultar: la columna Version emparejó correctamente las filas de cancelación y de estado a pesar de la inserción fuera de orden.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTree

Se recomienda usar SummingMergeTree junto con MergeTree. Almacene los datos detallados sin procesar en una tabla MergeTree y utilice SummingMergeTree para resultados preagregados. Esto evita la pérdida de datos si la clave primaria está compuesta incorrectamente.

SummingMergeTree preagrega filas con la misma clave primaria en una sola fila mediante la suma de columnas numéricas. Esto reduce el almacenamiento y acelera las consultas agregadas.

Notas de uso

• La preagregación ocurre durante la compactación en segundo plano, no al momento de la inserción. Es posible que algunos datos aún no estén agregados, por lo que siempre debe incluir una cláusula GROUP BY con SUM() en sus consultas.

• Solo se suman las columnas numéricas. Las columnas de tipo cadena y otras columnas no numéricas que no forman parte de la clave primaria obtienen un valor arbitrario después de la agregación.

Ejemplo: Preagregación con SummingMergeTree

1. Cree una tabla.

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

2. Inserte filas (algunas comparten la misma clave).

   INSERT INTO test_tbl_summing VALUES (1, 1), (1, 2), (2, 1);

3. Consulte antes de la compactación: las filas aún no están agregadas.

   SELECT * FROM test_tbl_summing;

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘

4. Fuerce la compactación.

   OPTIMIZE TABLE test_tbl_summing FINAL;

5. Consulte con GROUP BY y SUM() para obtener resultados correctos.

   SELECT key, SUM(value) FROM test_tbl_summing GROUP BY key;

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

AggregatingMergeTree

AggregatingMergeTree es un motor de preagregación más flexible que admite cualquier función de agregación, no solo SUM. Utiliza el tipo de dato especial AggregateFunction y un patrón de consulta de dos fases:

• Fase de escritura: use funciones con sufijo -State (por ejemplo, sumState, uniqState) para almacenar estados intermedios de agregación.

• Fase de consulta: use funciones con sufijo -Merge (por ejemplo, sumMerge, uniqMerge) para finalizar la agregación.

AggregatingMergeTree se utiliza de dos formas: con una vista materializada (que automatiza la fase de escritura) o directamente con columnas AggregateFunction.

Ejemplo 1: AggregatingMergeTree con una vista materializada

1. Cree una tabla de detalles.

   CREATE TABLE visits
   (
       UserID UInt64,
       CounterID UInt8,
       StartDate Date,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Cree una vista materializada que preagregue la tabla de detalles mediante funciones -State.

   CREATE MATERIALIZED VIEW visits_agg_view
   ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate)
   AS SELECT
       CounterID,
       StartDate,
       sumState(Sign)    AS Visits,
       uniqState(UserID) AS Users
   FROM visits
   GROUP BY CounterID, StartDate;

3. Inserte datos en la tabla de detalles. La vista materializada se actualiza automáticamente.

   INSERT INTO visits VALUES (0, 0, '2019-11-11', 1);
   INSERT INTO visits VALUES (1, 1, '2019-11-12', 1);

4. Consulte la vista materializada mediante funciones -Merge.

   Nota

   Use sumMerge y uniqMerge: las funciones simples sum y uniq no pueden procesar columnas AggregateFunction y devolverán un error.

   SELECT
       StartDate,
       sumMerge(Visits) AS Visits,
       uniqMerge(Users) AS Users
   FROM visits_agg_view
   GROUP BY StartDate
   ORDER BY StartDate;

   ┌──StartDate──┬─Visits─┬─Users──┐
   │  2019-11-11 │   1    │   1    │
   │  2019-11-12 │   1    │   1    │
   └─────────────┴────────┴────────┘

Ejemplo 2: AggregatingMergeTree con columnas AggregateFunction

1. Cree una tabla de detalles.

   CREATE TABLE detail_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID UInt64
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

2. Inserte datos.

   INSERT INTO detail_table VALUES (0, '2019-11-11', 1);
   INSERT INTO detail_table VALUES (1, '2019-11-12', 1);

3. Cree una tabla agregada con una columna AggregateFunction.

   CREATE TABLE agg_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID AggregateFunction(uniq, UInt64)
   ) ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

4. Inserte datos agregados mediante uniqState.

   Nota

   Inserte mediante una instrucción SELECT con funciones -State. Una instrucción directa INSERT INTO ... VALUES con valores sin procesar fallará con un error de tipo.

   INSERT INTO agg_table
   SELECT CounterID, StartDate, uniqState(UserID)
   FROM detail_table
   GROUP BY CounterID, StartDate;

5. Consulte la tabla agregada mediante uniqMerge.

   SELECT uniqMerge(UserID) AS state
   FROM agg_table
   GROUP BY CounterID, StartDate;

   ┌─state─┐
   │   1   │
   │   1   │
   └───────┘