PolarDB:Utiliser IMCI pour accélérer les traitements ETL

Fonctionnement

Cas d'utilisation

Privilégiez l'ETL via IMCI lorsque des conditions de requête complexes entraînent une exécution SQL longue, mais ne retournent qu'un faible volume de données :

• Pré-agrégation de rapports BI : synthétisez de grandes tables de faits en agrégats journaliers ou mensuels à l'aide de INSERT INTO ... SELECT. Les rapports en aval interrogent directement cette table de résultats réduite, ce qui évite des analyses complètes répétées sur les données source.

• Matérialisation de tables intermédiaires : précalculez et stockez les résultats de jointures ou d'agrégations complexes afin que les requêtes suivantes s'exécutent sur une table compacte plutôt que d'analyser systématiquement des gigaoctets de données brutes.

Évitez l'ETL via IMCI dans les situations suivantes :

• Requêtes simples : la lecture de données depuis des nœuds de stockage en colonnes distants engendre une surcharge liée à la transmission réseau et à l'analyse du jeu de résultats, susceptible de dégrader les performances.

• Jeux de résultats volumineux : si une requête retourne une grande quantité de données, leur transfert depuis les nœuds de stockage en colonnes vers le nœud primaire, puis leur écriture dans la table cible, risque de ralentir significativement le système.

Prérequis

Avant de commencer, assurez-vous que votre cluster PolarDB utilise l'une des versions suivantes :

• PolarDB for MySQL 8.0.1 avec une version de révision 8.0.1.1.29 ou ultérieure

• PolarDB for MySQL 8.0.2 avec une version de révision 8.0.2.2.12 ou ultérieure

Pour vérifier votre version, consultez la section « Query the engine version » de la rubrique Versions du moteur.

Limites

L'ETL via IMCI s'applique uniquement aux instructions SQL suivantes :

• CREATE TABLE table_name [AS] SELECT ...

• INSERT ... SELECT ...

Activer l'ETL via IMCI

Deux paramètres contrôlent cette fonctionnalité :

**Valeurs de etl_from_imci :**

• ON : route les instructions SELECT vers les nœuds de stockage en colonnes en lecture seule.

• OFF (par défaut) : exécute les instructions SELECT sur le nœud primaire.

• FORCED : force le routage des instructions SELECT vers les nœuds de stockage en colonnes en lecture seule, même si l'instruction se trouve dans une transaction active.

Définissez ce paramètre à trois niveaux différents. Plus le niveau est élevé, plus la portée est large ; les hints au niveau de l'instruction remplacent les paramètres de session et globaux pour une requête donnée.

Niveau global — s'applique à toutes les instructions CREATE TABLE ... SELECT et INSERT ... SELECT du cluster :

SET GLOBAL etl_from_imci = ON;

Niveau session — concerne uniquement les instructions de la session en cours :

SET etl_from_imci = ON;

Niveau instruction — cible une seule instruction au moyen d'un hint d'optimiseur :

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

Vérifier l'activation de la fonctionnalité

Exemple de bout en bout

L'exemple suivant matérialise un résumé quotidien des frais à partir d'une table de transactions brute. L'agrégation SELECT s'exécute sur le nœud de stockage en colonnes en lecture seule, tandis que le nœud primaire écrit le résultat dans la table de synthèse.

Créez les tables source et de destination :

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)
);

Activez l'ETL via IMCI pour la session et insérez les données agrégées :

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;

Interrogez la table de synthèse compacte pour obtenir les totaux mensuels :

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

L'agrégation de l'instruction INSERT INTO ... SELECT s'exécute sur le nœud de stockage en colonnes et se termine en moins d'une seconde pour des jeux de données de 10 Go (voir Comparaison des performances). Les consolidations mensuelles ultérieures portent sur la petite table de synthèse plutôt que sur l'historique complet des transactions.

Comparaison des performances

Les résultats ci-dessous utilisent 10 Go de données générées avec TPC Benchmark H (TPC-H) pour comparer les temps de requête (en secondes) d'agrégations complexes.

Requête retournant 1 ligne :

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

Requête retournant 4 lignes :

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

Requête retournant 27 840 lignes :

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

À mesure que le jeu de résultats s'étoffe (de 1 à 27 840 lignes), la surcharge liée au transfert des résultats du nœud de stockage en colonnes vers le nœud primaire augmente. Pour les requêtes qui retournent des jeux de résultats très volumineux, évaluez si le gain de performance justifie le coût de transfert.