PolarDB:Uso de IMCI para acelerar ETL

Funcionamiento

Escenarios de uso

Utilice ETL from IMCI cuando se especifiquen condiciones de consulta complejas, el tiempo de ejecución de las instrucciones SQL sea prolongado y solo se devuelva un volumen pequeño de datos:

• Preagregación de informes de BI: resuma tablas de hechos grandes en agregados diarios o mensuales mediante INSERT INTO ... SELECT. Los informes posteriores consultan directamente la tabla de resultados pequeña, lo que evita exploraciones completas repetidas sobre los datos de origen.

• Materialización de tablas intermedias: precalcule y almacene resultados complejos de uniones o agregaciones. De este modo, las consultas posteriores se ejecutan contra una tabla compacta en lugar de explorar gigabytes de datos sin procesar cada vez.

Evite usar ETL from IMCI en los siguientes escenarios:

• Consultas simples: leer datos de nodos remotos de almacenamiento en columnas de solo lectura genera costos adicionales por transmisión de red y análisis sintáctico del conjunto de resultados, lo cual puede degradar el rendimiento.

• Conjuntos de resultados grandes: si el conjunto de resultados contiene un gran volumen de datos, leerlos desde nodos remotos de almacenamiento en columnas de solo lectura, enviarlos al nodo primario y escribirlos en la tabla de dicho nodo puede provocar una degradación del rendimiento.

Requisitos previos

Antes de empezar, asegúrese de que su clúster de PolarDB utiliza una de las siguientes versiones:

• PolarDB for MySQL 8.0.1 con versión de revisión 8.0.1.1.29 o posterior

• PolarDB for MySQL 8.0.2 con versión de revisión 8.0.2.2.12 o posterior

Para comprobar su versión, consulte la sección "Query the engine version" en Engine versions.

Limitaciones

ETL from IMCI solo aplica a las siguientes instrucciones SQL:

• CREATE TABLE table_name [AS] SELECT ...

• INSERT ... SELECT ...

Habilitar ETL from IMCI

Dos parámetros controlan esta función:

**Valores de etl_from_imci:**

• ON: enruta SELECT a los nodos de almacenamiento en columnas de solo lectura.

• OFF (valor predeterminado): ejecuta SELECT en el nodo primario.

• FORCED: enruta SELECT a los nodos de almacenamiento en columnas de solo lectura, incluso si la instrucción está dentro de una transacción activa.

Establezca el parámetro en cualquiera de tres niveles. Los niveles superiores afectan un alcance más amplio; las sugerencias a nivel de instrucción anulan la configuración de sesión y global para una sola consulta.

Nivel global: aplica a todas las instrucciones CREATE TABLE ... SELECT y INSERT ... SELECT del clúster:

SET GLOBAL etl_from_imci = ON;

Nivel de sesión: aplica únicamente a las instrucciones de la sesión actual:

SET etl_from_imci = ON;

Nivel de instrucción: aplica a una sola instrucción mediante una sugerencia del optimizador:

CREATE TABLE t2 SELECT /*+ SET_VAR(etl_from_imci=ON) */ * FROM t1 WHERE 'A' = 'a';

Comprobar que la función está activa

Ejemplo integral

El siguiente ejemplo materializa un resumen diario de comisiones a partir de una tabla de transacciones sin procesar. La agregación SELECT se ejecuta en el nodo de almacenamiento en columnas de solo lectura, mientras que el nodo primario escribe el resultado en la tabla de resumen.

Cree las tablas de origen y de destino:

CREATE TABLE transaction_detail (
    ts          DATETIME,
    customer_id VARCHAR(20),
    fee         DECIMAL(20, 2)
);

CREATE TABLE daily_summary (
    rec_date    DATE,
    customer_id VARCHAR(20),
    daily_fee   DECIMAL(20, 2)
);

Habilite ETL from IMCI para la sesión e inserte los datos agregados:

SET etl_from_imci = ON;

INSERT INTO daily_summary (rec_date, customer_id, daily_fee)
SELECT
    DATE(ts),
    customer_id,
    SUM(fee)
FROM transaction_detail
WHERE DATE(ts) > '2024-01-01'
GROUP BY DATE(ts), customer_id;

Consulte la tabla de resumen compacta para obtener los totales mensuales:

SELECT
    MONTH(rec_date),
    customer_id,
    SUM(daily_fee)
FROM daily_summary
GROUP BY MONTH(rec_date), customer_id;

La agregación en INSERT INTO ... SELECT se ejecuta en el nodo de almacenamiento en columnas y finaliza en menos de un segundo con conjuntos de datos de 10 GB (consulte Comparación de rendimiento). Las consolidaciones mensuales posteriores se ejecutan contra la tabla de resumen pequeña, no contra todo el historial de transacciones.

Comparación de rendimiento

Los siguientes resultados utilizan 10 GB de datos generados con TPC Benchmark H (TPC-H) para comparar los tiempos de consulta (en segundos) de agregaciones complejas.

Consulta que devuelve 1 fila:

SELECT
    SUM(l_extendedprice * l_discount) AS revenue
FROM
    lineitem
WHERE
    l_shipdate >= DATE '1994-01-01'
    AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    AND l_discount BETWEEN .06 - 0.01 AND .06 + 0.01
    AND l_quantity < 24

Consulta que devuelve 4 filas:

SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity)                                       AS sum_qty,
    SUM(l_extendedprice)                                  AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount))               AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity)                                       AS avg_qty,
    AVG(l_extendedprice)                                  AS avg_price,
    AVG(l_discount)                                       AS avg_disc,
    COUNT(*)                                              AS count_order
FROM
    lineitem
WHERE
    l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus

Consulta que devuelve 27,840 filas:

SELECT
    p_brand,
    p_type,
    p_size,
    COUNT(DISTINCT ps_suppkey) AS supplier_cnt
FROM
    partsupp,
    part
WHERE
    p_partkey = ps_partkey
    AND p_brand <> 'Brand#45'
    AND p_type NOT LIKE 'MEDIUM POLISHED%'
    AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
    AND ps_suppkey NOT IN (
        SELECT
            s_suppkey
        FROM
            supplier
        WHERE
            s_comment LIKE '%Customer%Complaints%'
    )
GROUP BY
    p_brand,
    p_type,
    p_size
ORDER BY
    supplier_cnt DESC,
    p_brand,
    p_type,
    p_size

A medida que crece el conjunto de resultados (de 1 a 27,840 filas), aumenta el costo de transferir los resultados del nodo de almacenamiento en columnas al nodo primario. Para consultas que devuelven conjuntos de resultados muy grandes, evalúe si la mejora justifica el costo de transferencia.