Reescrita de consulta de tabela dinâmica - Hologres: Central de Documentação do Alibaba Cloud

Este tópico descreve o recurso de reescrita de consulta para Tabelas Dinâmicas do Hologres, incluindo uso e limitações.

Reescrita de consulta

Em cenários de big data e data warehouse, as tabelas de detalhes costumam ser massivas, com centenas de milhões ou dezenas de bilhões de linhas. Consultas analíticas e de negócios dependem fortemente de agregações GROUP BY multidimensionais nessas tabelas, como calcular volumes diários ou horários de pedidos e GMV por cidade, ou rastrear PV/UV e taxas de conversão por canal ou dispositivo. Executar essas agregações diretamente em uma tabela de detalhes causa vários problemas:

Custos elevados de agregação: cada consulta verifica e agrega grandes partes ou toda a tabela de detalhes, consumindo recursos significativos de CPU e I/O.
Sobrecarga na tabela de detalhes: isso pode afetar outras tarefas no mesmo banco de dados e exigir dimensionamento frequente.

As Tabelas Dinâmicas do Hologres oferecem recursos de reescrita de consulta. Se uma Tabela Dinâmica pré-agregou uma tabela base, o otimizador poderá reescrever automaticamente uma consulta de agregação na tabela base para consultar a Tabela Dinâmica, desde que certas condições sejam atendidas. Essa abordagem evita o custo computacional da agregação e oferece benefícios essenciais:

Redução da carga de agregação nas tabelas de detalhes: métricas frequentes, como contagem de pedidos, GMV e PV/UV, podem ser lidas diretamente dos resultados pré-agregados em uma Tabela Dinâmica, minimizando varreduras e agregações repetitivas na tabela de detalhes.
Melhoria significativa nos tempos de resposta: para relatórios, análises self-service e consultas interativas que acessam uma Tabela Dinâmica, a agregação é substancialmente reduzida, diminuindo a latência. A experiência assemelha-se à consulta de uma tabela ampla.
Transparência para usuários upstream: analistas de dados e desenvolvedores de aplicativos podem continuar consultando a tabela base sem alterações. A equipe de plataforma ou data warehouse projeta e mantém as Tabelas Dinâmicas, tornando as otimizações de desempenho invisíveis aos usuários.

A reescrita de consulta de Tabela Dinâmica do Hologres é adequada para os seguintes casos de uso:

Painéis operacionais e monitoramento em tempo real ou near-real-time.
Análise de BI multidimensional e acesso self-service a dados.
Aceleração de um sistema unificado de métricas principais, como GMV, volume de pedidos e contagem de usuários ativos.

Uso e limitações

Requisito de versão: este recurso exige Hologres V4.1 ou posterior.
Consistência da consulta: a reescrita utiliza os dados da atualização mais recente da Tabela Dinâmica. Isso significa que os dados ficam defasados em relação ao estado mais recente da tabela base, resultando em consistência fraca.
Limitações da tabela base:
- Tipos suportados: tabelas internas do Hologres, foreign tables Paimon (criadas como Foreign Tables) e foreign tables MaxCompute (criadas como Foreign Tables).
- Se a tabela base for particionada no Hologres, partições físicas não são suportadas. No entanto, a tabela base pode usar partições lógicas.
- External Tables não são suportadas.
Limitações do tipo de Tabela Dinâmica:
- Suportado: Tabelas Dinâmicas não particionadas e logicamente particionadas.
- Não suportado: Tabelas Dinâmicas fisicamente particionadas e External Dynamic Tables.
Limitações na definição de consulta em uma Tabela Dinâmica:
- Atualmente, apenas consultas de tabela única são suportadas.
- Funções de agregação com cláusula FILTER, como sum(x) FILTER (WHERE ...), não são suportadas.
- A consulta não pode calcular novas colunas a partir de resultados de agregação na lista SELECT, como sum(x)/count(x).

Ativar e configurar a reescrita de consulta

Recomendação: este recurso é adequado para painéis, monitoramento e cenários analíticos que toleram latência de segundos a minutos. Para cenários que exigem garantias fortes de tempo real ou reconciliação rigorosa de dados, recomendamos consultar a tabela base diretamente ou usar outras soluções de consistência forte.

Ativar a reescrita de consulta

Ao consultar uma tabela base, defina o parâmetro GUC hg_enable_query_rewrite para controlar se a consulta pode usar a reescrita.

Nota

Não recomendamos ativar este recurso no nível do banco de dados, pois isso pode causar degradação de desempenho.

-- Enable query rewrite (session level)
SET hg_enable_query_rewrite = on;
-- Set at the database level (not recommended)
ALTER DATABASE <db_name> SET hg_enable_query_rewrite = on;

Ativar a reescrita de consulta para uma Tabela Dinâmica

Ao criar uma Tabela Dinâmica, use a propriedade allowed_to_rewrite_query para controlar se ela pode ser usada para reescrita de consulta. Por padrão, essa propriedade é definida como 'false' e a tabela não é utilizada para reescrita.

CREATE [ OR REPLACE ] DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name> (
  [col_name],
  [col_name],
  [col_name]
)
[LOGICAL PARTITION BY LIST(<partition_key>)]
WITH (
  ...,
  allowed_to_rewrite_query = '[true | false]',
  ...
)
AS
<query>;

Descrição do parâmetro:

allowed_to_rewrite_query: especifica se esta Tabela Dinâmica é candidata para reescrita de consulta.
- 'true': permite que a tabela seja usada para reescrita de consulta.
- 'false': valor padrão. A tabela não é usada para reescrita de consulta.

Recomendações:

Para Tabelas Dinâmicas criadas especificamente para acelerar consultas de agregação, defina esta propriedade como 'true'.
Para Tabelas Dinâmicas com definições complexas incompatíveis com as regras atuais de reescrita, defina esta propriedade como 'false' para reduzir sobrecarga desnecessária do otimizador.

Modificar a propriedade de reescrita de consulta

Use ALTER DYNAMIC TABLE ... SET para alterar se uma Tabela Dinâmica pode ser usada para reescrita de consulta:

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
SET (allowed_to_rewrite_query = '[true | false]');

Controlar Tabelas Dinâmicas candidatas

Se houver várias Tabelas Dinâmicas disponíveis, especifique um conjunto de tabelas candidatas em uma dica (hint) para restringir o escopo de busca do otimizador e controlar a prioridade. Para obter mais informações sobre a sintaxe de dicas, consulte HINT.

SELECT /*+HINT query_rewrite_candidates(<schema.dt_name1> <schema.dt_name2> ...) */
  ...
FROM ...;

Notas de uso:

Se houver múltiplas Tabelas Dinâmicas, separe-as com espaços.
Inclua o nome do schema, se necessário.

Exemplo:

-- Only allow dt_sales to be used for query rewrite
SELECT /*+HINT query_rewrite_candidates(dt_sales) */
  day, hour, min(amount), max(amount)
FROM base_sales_table
GROUP BY day, hour;

Recursos suportados

A versão atual suporta reescrita de consulta para agregação de tabela única em três padrões principais:

Reescrita transparente com dimensões de agregação correspondentes.
Agregação Rollup (agregação em um subconjunto das dimensões da Tabela Dinâmica).
Agregação Rollup com condições de filtro.

Dimensões de agregação correspondentes

Condições:

As dimensões GROUP BY na consulta correspondem exatamente às dimensões GROUP BY na definição da Tabela Dinâmica.
As funções de agregação na consulta podem ser satisfeitas pelas colunas agregadas na Tabela Dinâmica.
Qualquer tipo de função de agregação, incluindo DISTINCT, é suportado, desde que exista uma coluna de resultado correspondente na Tabela Dinâmica.

Exemplo:

-- Create the base table
CREATE TABLE base_sales_table(
    day text not null,
    hour int,
    amount int
);
-- Insert data
INSERT INTO base_sales_table
VALUES  ('20250529', 12, 1),
        ('20250529', 12, 2),
        ('20250529', 12, 2),
        ('20250529', 13, 3),
        ('20250530', 13, 4),
        ('20250530', 14, 5),
        ('20250531', 14, 6);
-- Create the Dynamic Table
CREATE DYNAMIC TABLE dt_sales
WITH (
    freshness = '1 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true'
)
AS
SELECT
    day,
    hour,
    min(amount),
    max(amount),
    sum(amount),
    count(amount),
    count(*) as rows,
    count(1) as rows1,
    count(distinct amount) as cd
FROM base_sales_table
GROUP BY day, hour;
-- Manually refresh the Dynamic Table
REFRESH TABLE dt_sales;

Exemplo de consulta: quando as dimensões de agregação correspondem, o plano de execução mostra que a consulta na tabela base foi reescrita para consultar a Tabela Dinâmica.

-- The query dimensions match the Dynamic Table
EXPLAIN SELECT day, hour, min(amount), max(amount) FROM base_sales_table GROUP BY day, hour;

O plano de execução retornado mostra que a consulta foi reescrita para realizar uma varredura sequencial na Tabela Dinâmica dt_sales:

QUERY PLAN
Gather  (cost=0.00..5.00 rows=7 width=20)
  -> Local Gather  (cost=0.00..5.00 rows=7 width=20)
    -> Project  (cost=0.00..5.00 rows=7 width=20)
      -> Seq Scan on dt_sales  (cost=0.00..5.00 rows=5 width=20)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.1.0

Agregação Rollup

Condições:

As dimensões GROUP BY da Tabela Dinâmica são um superconjunto das dimensões GROUP BY na consulta. Em outras palavras, a Tabela Dinâmica está agregada em uma granularidade mais fina.
As funções de agregação na consulta podem ser derivadas agregando-se as colunas na Tabela Dinâmica.
Funções de agregação suportadas: min, max, count, sum e avg.
Agregação Rollup com DISTINCT não é suportada, exceto no cenário de dimensões correspondentes onde o resultado pode ser lido diretamente da Tabela Dinâmica.

Mapeamento de funções de agregação:

Função de agregação original	Coluna de agregação necessária	Função de agregação reescrita
`sum(x)`	`sum(x)`	`sum(sum)`
`count(x)`	`count(x)`	`sum(count)`
`min(x)`	`min(x)`	`min(min)`
`max(x)`	`max(x)`	`max(max)`
`avg(x)`	`sum(x)` e `count(x)`	`sum(sum) / sum(count)`

Exemplo:

-- Create the base table
CREATE TABLE base_sales_table(
    day text not null,
    hour int,
    amount int
);
-- Insert data
INSERT INTO base_sales_table
VALUES  ('20250529', 12, 1),
        ('20250529', 12, 2),
        ('20250529', 12, 2),
        ('20250529', 13, 3),
        ('20250530', 13, 4),
        ('20250530', 14, 5),
        ('20250531', 14, 6);
-- Create the Dynamic Table. To verify the rewrite, we first disable automatic refresh.
CREATE DYNAMIC TABLE dt_sales
WITH (
    freshness = '1 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true'
)
AS
SELECT
    day,
    hour,
    min(amount),
    max(amount),
    sum(amount),
    count(amount),
    count(*) as rows,
    count(1) as rows1,
    count(distinct amount) as cd
FROM base_sales_table
GROUP BY day, hour;
-- Manually refresh the Dynamic Table
REFRESH TABLE dt_sales;

Exemplo de consulta 1: agregação por day (rollup). A coluna GROUP BY na consulta é um subconjunto das dimensões na definição da Tabela Dinâmica, portanto, a consulta pode ser reescrita.

-- Original query
EXPLAIN SELECT day, min(amount), max(amount)
FROM base_sales_table
GROUP BY day;

O plano de execução mostra que o operador de varredura de nível mais baixo é Seq Scan on dt_sales, indicando que a consulta lê da Tabela Dinâmica em vez da tabela base base_sales_table.

QUERY PLAN
Gather  (cost=0.00..5.00 rows=4 width=16)
  ->  HashAggregate  (cost=0.00..5.00 rows=4 width=16)
        Group Key: day
        ->  Redistribution  (cost=0.00..5.00 rows=5 width=16)
              Hash Key: day
              ->  Local Gather  (cost=0.00..5.00 rows=5 width=16)
                    ->  Seq Scan on dt_sales  (cost=0.00..5.00 rows=5 width=16)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.1.0

Exemplo de consulta 2: rollup de sum + count + avg. A consulta na tabela base usa avg. Como a Tabela Dinâmica contém valores pré-computados de sum e count, é possível derivar avg e a consulta é reescrita.

-- Original query
EXPLAIN SELECT day, sum(amount), count(amount), avg(amount)
FROM base_sales_table
GROUP BY day;

O plano de execução mostra que a consulta foi reescrita para varrer a Tabela Dinâmica dt_sales. Observe que o operador subjacente é Seq Scan on dt_sales, não base_sales_table.

QUERY PLAN
Gather  (cost=0.00..5.00 rows=4 width=32)
  -> Project  (cost=0.00..5.00 rows=4 width=32)
    -> Project  (cost=0.00..5.00 rows=4 width=40)
      -> HashAggregate  (cost=0.00..5.00 rows=4 width=24)
            Group Key: day
            -> Redistribution  (cost=0.00..5.00 rows=5 width=24)
                  Hash Key: day
                  -> Local Gather  (cost=0.00..5.00 rows=5 width=24)
                        -> Seq Scan on dt_sales  (cost=0.00..5.00 rows=5 width=24)
Query Queue: init_warehouse.default.queue
Optimizer: HQO version 4.1.0

Agregação Rollup com condições de filtro

Condições:

A consulta na tabela base inclui uma cláusula WHERE, enquanto a definição da Tabela Dinâmica não pode ter uma cláusula WHERE.
Todas as colunas usadas na cláusula WHERE devem fazer parte das dimensões GROUP BY da Tabela Dinâmica.
As funções de agregação suportadas são min, max, count, sum e avg. DISTINCT não é suportado.

Exemplo:

-- Create the base table
CREATE TABLE base_sales_table(
    day text not null,
    hour int,
    amount int
);
-- Insert data
INSERT INTO base_sales_table
VALUES  ('20250529', 12, 1),
        ('20250529', 12, 2),
        ('20250529', 12, 2),
        ('20250529', 13, 3),
        ('20250530', 13, 4),
        ('20250530', 14, 5),
        ('20250531', 14, 6);
-- Create the Dynamic Table. To verify the rewrite, we first disable automatic refresh.
CREATE DYNAMIC TABLE dt_sales
WITH (
    freshness = '1 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true'
)
AS
SELECT
    day,
    hour,
    min(amount),
    max(amount),
    sum(amount),
    count(amount),
    count(*) as rows,
    count(1) as rows1,
    count(distinct amount) as cd
FROM base_sales_table
GROUP BY day, hour;
-- Manually refresh the Dynamic Table
REFRESH TABLE dt_sales;

No exemplo a seguir, a consulta na tabela base possui uma cláusula WHERE e as colunas de filtro fazem parte da chave GROUP BY. Portanto, a consulta pode ser reescrita.

EXPLAIN SELECT day, sum(amount), count(amount), avg(amount)
FROM base_sales_table
WHERE day > '20250528' AND day <= '20250531'
GROUP BY day;

Executar esta instrução EXPLAIN retorna um plano de consulta onde o operador Seq Scan on dt_sales confirma que a consulta foi reescrita para varrer a Tabela Dinâmica em vez da tabela base. A condição de filtro corresponde à cláusula WHERE original.

QUERY PLAN
Gather  (cost=0.00..5.00 rows=1 width=32)
  ->  Project  (cost=0.00..5.00 rows=1 width=32)
    ->  Project  (cost=0.00..5.00 rows=1 width=40)
      ->  HashAggregate  (cost=0.00..5.00 rows=1 width=24)
            Group Key: day
        ->  Redistribution  (cost=0.00..5.00 rows=1 width=24)
              Hash Key: day
          ->  Local Gather  (cost=0.00..5.00 rows=1 width=24)
            ->  Seq Scan on dt_sales  (cost=0.00..5.00 rows=1 width=24)
                  Filter: ((day > '20250528'::text) AND (day <= '20250531'::text))
                  RowGroupFilter: ((day > '20250528'::text) AND (day <= '20250531'::text))
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.1.0

Verificar o status da reescrita de consulta

Após ativar a reescrita de consulta, verifique se uma consulta utilizou uma Tabela Dinâmica das seguintes maneiras:

Verifique o plano de execução: na saída do EXPLAIN, confira o nome da tabela no operador Scan para determinar se a consulta usou uma Tabela Dinâmica.
Consulte o log de consultas lentas: na tabela hologres.hg_query_log, o campo extended_info registra qual Tabela Dinâmica foi usada para a reescrita. Se uma reescrita falhar, esse campo incluirá o motivo da falha.

select extended_info::json->>'rewrite_query_info' from hologres.hg_query_log where query_id = 'xxxxx';
                                                                                                               ?column?                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"rewrite_failed_dt": [[\"public.dt3\", {\"rewrite_failed_cause\": \"Doesn't include all query required output columns\"}]], \"rewrite_succeeded_and_selected_dt\": [\"public.dt2\"], \"rewrite_succeeded_but_not_selected_dt\": [\"public.dt1\"]}
(1 row)

Exemplos

Exemplo 1: Tabela interna do Hologres

A tabela base é a tabela lineitem de 100 GB do conjunto de dados TPC-H. Para instruções de criação de tabela e importação de dados, consulte Importar conjuntos de dados públicos com um clique. Neste exemplo, a Tabela Dinâmica usa atualização incremental e não é particionada.

CREATE DYNAMIC TABLE dt_lineitem_100g_incremental
WITH (
    freshness = '10 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true')
AS 
select
        l_returnflag,
        l_linestatus,
        l_shipdate,
        sum(l_quantity) as sum_qty,
        count(*) as count_order
from
        hologres_dataset_tpch_100g.lineitem
group by
        l_returnflag,
        l_linestatus,
        l_shipdate;
-- Manually refresh
REFRESH DYNAMIC TABLE dt_lineitem_100g_incremental;

Consulte a tabela base:

set hg_enable_query_rewrite = on;
explain 
select
        l_returnflag,
        l_linestatus,
        l_shipdate,
        sum(l_quantity) as sum_qty,
        count(*) as count_order
from
        hologres_dataset_tpch_100g.lineitem
where  l_shipdate =  '1998-12-01' 
group by
        l_returnflag,
        l_linestatus,
        l_shipdate;

O plano de execução mostra que a consulta foi reescrita para consultar a Tabela Dinâmica:

QUERY PLAN
Gather  (cost=0.00..5.00 rows=1 width=22)
  -> Local Gather  (cost=0.00..5.00 rows=1 width=22)
    -> Project  (cost=0.00..5.00 rows=1 width=22)
      -> Seq Scan on dt_lineitem_100g_incremental  (cost=0.00..5.00 rows=1 width=20)
            Filter: (l_shipdate = '1998-12-01'::date)
            RowGroupFilter: (l_shipdate = '1998-12-01'::date)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.1.0

O resultado da consulta à tabela base é o seguinte:

 l_returnflag | l_linestatus | l_shipdate | sum_qty  | count_order 
--------------+--------------+------------+----------+-------------
 N            | O            | 1998-12-01 | 52841.00 |        2070
(1 row)

Consultar a Tabela Dinâmica diretamente retorna o mesmo resultado, consistente com a última atualização manual (a atualização automática foi desativada para este exemplo).

select
        l_returnflag,
        l_linestatus,
        l_shipdate,
        sum_qty,
        count_order
from
        dt_lineitem_100g_incremental
where  l_shipdate =  '1998-12-01' ;
 l_returnflag | l_linestatus | l_shipdate | sum_qty  | count_order 
--------------+--------------+------------+----------+-------------
 N            | O            | 1998-12-01 | 52841.00 |        2070
(1 row)

Exemplo 2: Foreign table Paimon

A reescrita de consulta também pode ser usada quando a tabela base é uma foreign table Paimon. Siga estas etapas:

Prepare uma tabela Paimon: neste exemplo, importe a tabela customer de 100 GB do TPC-H para o Paimon. Para mais informações, consulte Tabela Paimon.
Crie uma foreign table Paimon no Hologres: crie a tabela como Foreign Table. Para detalhes, consulte Acessar dados Paimon via DLF Catalog.

-- Create a foreign server
CREATE SERVER IF NOT EXISTS paimon_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    catalog_type 'paimon',
    metastore_type 'dlf-rest', 
    dlf_catalog '<dlf_catalog_name>'
);
-- Use IMPORT FOREIGN SCHEMA to create the Paimon foreign table
IMPORT FOREIGN SCHEMA <schema_name>
limit to (customer) 
FROM SERVER paimon_server into public
options (if_table_exist 'update');
-- Query data
SELECT * FROM customer;

Crie uma Tabela Dinâmica para consumir incrementalmente a foreign table Paimon: no Hologres, crie uma Tabela Dinâmica que atualize incrementalmente a partir da foreign table Paimon. Para este exemplo, a atualização automática está desativada para facilitar a verificação da reescrita.

-- Create the Dynamic Table
CREATE DYNAMIC TABLE dt_paimon_customer
WITH (
    freshness = '10 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true')
AS
SELECT
     c_custkey,
     avg(c_acctbal) ,
     sum(c_acctbal) ,
     count(c_acctbal)
FROM customer
group by c_custkey;
-- Manually refresh the Dynamic Table
REFRESH DYNAMIC TABLE dt_paimon_customer;

Consulte a foreign table Paimon com a reescrita de consulta ativada.

set hg_enable_query_rewrite = on;
SELECT
     c_custkey,
     avg(c_acctbal) ,
     sum(c_acctbal) ,
     count(c_acctbal)
FROM
   customer
group by c_custkey ORDER BY 3 DESC  LIMIT 3;
c_custkey	| avg	        |sum	    |count
----------|-------------|---------|-----
3605586	  |9999.990000  |	9999.99	|1
10705496	|9999.990000	|9999.99	|1
14959900	|9999.990000	|9999.99	|1

Consulte a Tabela Dinâmica: o resultado reflete os dados da atualização mais recente.

SELECT * FROM dt_paimon_customer ORDER BY 3 DESC  LIMIT 3;
c_custkey	| avg	        |sum	    |count
----------|-------------|---------|-----
3605586	  |9999.990000  |	9999.99	|1
10705496	|9999.990000	|9999.99	|1
14959900	|9999.990000	|9999.99	|1

Confirme com o plano de execução: o plano mostra que a consulta foi reescrita para acessar a Tabela Dinâmica.

QUERY PLAN
Limit  (cost=0.00..5.07 rows=1 width=28)
  ->  Project  (cost=0.00..5.07 rows=1 width=28)
    ->  Limit  (cost=0.00..5.07 rows=1 width=36)
      ->  Sort  (cost=0.00..5.07 rows=1 width=36)
            Sort Key: sum DESC
            ->  Gather  (cost=0.00..5.07 rows=1 width=36)
              ->  Local Gather  (cost=0.00..5.07 rows=1 width=36)
                ->  Limit  (cost=0.00..5.07 rows=1 width=36)
                  ->  Sort  (cost=0.00..5.07 rows=1 width=36)
                        Sort Key: sum DESC
                        ->  Project  (cost=0.00..5.07 rows=1 width=36)
                          ->  Project  (cost=0.00..5.07 rows=1 width=20)
                            ->  Seq Scan on dt_paimon_customer  (cost=0.00..5.07 rows=15000000 width=17)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.1.0