PolarDB:Use o IMCI para acelerar ETL

Como funciona

Casos de uso

Use o ETL from IMCI quando houver condições de consulta complexas e o tempo de execução das instruções SQL for longo, mas o volume de dados retornado for pequeno:

• Pré-agregação de relatórios de BI: Resuma grandes tabelas de fatos em agregações diárias ou mensais com INSERT INTO ... SELECT. Os relatórios downstream consultam diretamente a pequena tabela de resultados, evitando varreduras completas repetidas nos dados de origem.

• Materialização de tabelas intermediárias: Pré-calcule e armazene resultados complexos de junção ou agregação. Assim, as consultas subsequentes são executadas em uma tabela compacta, em vez de varrer gigabytes de dados brutos a cada execução.

Evite o ETL from IMCI nos seguintes cenários:

• Consultas simples: Ler dados em nós remotos de column store somente leitura gera sobrecarga adicional de transmissão de rede e análise do conjunto de resultados, o que pode degradar o desempenho.

• Conjuntos de resultados grandes: Se a consulta retornar um volume elevado de dados, ler informações nos nós remotos de column store somente leitura, enviá-las ao nó primário e gravá-las na tabela pode causar degradação de desempenho.

Pré-requisitos

Antes de começar, verifique se o cluster PolarDB usa uma das seguintes versões:

• PolarDB for MySQL 8.0.1 com versão de revisão 8.0.1.1.29 ou posterior

• PolarDB for MySQL 8.0.2 com versão de revisão 8.0.2.2.12 ou posterior

Para verificar sua versão, consulte a seção "Query the engine version" em Versões do mecanismo.

Limitações

O ETL from IMCI aplica-se apenas às seguintes instruções SQL:

• CREATE TABLE table_name [AS] SELECT ...

• INSERT ... SELECT ...

Ative o ETL from IMCI

Dois parâmetros controlam esse recurso:

**Valores de etl_from_imci:**

• ON: Encaminha o SELECT para os nós de column store somente leitura.

• OFF (padrão): Executa o SELECT no nó primário.

• FORCED: Encaminha o SELECT para os nós de column store somente leitura mesmo que a instrução esteja dentro de uma transação ativa.

Defina o parâmetro em qualquer um dos três níveis. Níveis mais altos afetam um escopo mais amplo; dicas no nível de instrução substituem as configurações de sessão e globais para uma única consulta.

Nível global — aplica-se a todas as instruções CREATE TABLE ... SELECT e INSERT ... SELECT no cluster:

SET GLOBAL etl_from_imci = ON;

Nível de sessão — aplica-se apenas às instruções da sessão atual:

SET etl_from_imci = ON;

Nível de instrução — aplica-se a uma única instrução por meio de uma dica do otimizador:

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

Verifique se o recurso está ativo

Exemplo de ponta a ponta

O exemplo a seguir materializa um resumo diário de taxas a partir de uma tabela bruta de transações. A agregação SELECT é executada no nó de column store somente leitura; o nó primário grava o resultado na tabela de resumo.

Crie as tabelas de source e 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)
);

Ative o ETL from IMCI para a sessão e insira os dados 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 a tabela de resumo compacta para obter totais mensais:

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

A agregação em INSERT INTO ... SELECT é executada no nó de column store e conclui em menos de um segundo para conjuntos de dados de 10 GB (consulte Comparação de desempenho). Os rollups mensais subsequentes são executados na pequena tabela de resumo, e não em todo o histórico de transações.

Comparação de desempenho

Os resultados abaixo usam 10 GB de dados gerados com o TPC Benchmark H (TPC-H) para comparar tempos de consulta (em segundos) em agregações complexas.

Consulta retornando 1 linha:

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 retornando 4 linhas:

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 retornando 27.840 linhas:

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

À medida que o conjunto de resultados cresce (de 1 linha para 27.840 linhas), aumenta também a sobrecarga de transferência dos resultados do nó de column store para o nó primário. Para consultas que retornam conjuntos de resultados muito grandes, avalie se a melhoria de desempenho justifica o custo dessa transferência.