Hologres dynamic partitioning - automatic partition management - Hologres: Central de Documentação do Alibaba Cloud

Visão geral

Suporte ao recurso por versão:

Hologres V1.3 e posteriores: configuração de regras de particionamento dinâmico. O sistema cria periodicamente partições filhas com antecedência e exclui as expiradas com base nessas regras.
Hologres V1.3.37 e posteriores: o armazenamento em camadas dinâmico move dados para o cold storage automaticamente para reduzir custos. Armazenamento em camadas.
Hologres V2.1.11 e posteriores: suporte ao tipo Date como chave de partição.
Hologres V2.2 e posteriores: uso da propriedade schd_start_time para personalizar o horário de execução da criação, exclusão e migração de partições para o cold storage.
Hologres V3.0.12 e posteriores: uso de auto_partitioning_time_format para especificar o formato de data das chaves de partição, como YYYY-MM-DD.

Limites

A propriedade schd_start_time aplica-se apenas à tabela pai, não às partições filhas.
O parâmetro auto_partitioning_time_format só pode ser definido uma vez na instrução CREATE TABLE. Não é possível modificá-lo em tabelas existentes.
Quando auto_partitioning_time_format está definido, os nomes das partições filhas usam o formato de data especificado. Coloque os nomes das tabelas filhas entre aspas duplas ao consultar. Exemplo: SELECT xxx FROM "tbl_2024_11_22".

Configurar particionamento dinâmico

Descrição da sintaxe

Defina as propriedades de particionamento dinâmico ao criar uma tabela ou modifique-as posteriormente:

Exemplo usando a cláusula WITH

Configure as propriedades de gerenciamento de particionamento dinâmico ao criar uma tabela particionada.

-- Configure dynamic partition management properties when creating a partitioned table.
CREATE TABLE [IF NOT EXISTS] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>)
WITH (
   auto_partitioning_enable = 'xxx',
   auto_partitioning_time_unit = 'xxx',
   auto_partitioning_time_zone = 'xxx',
   auto_partitioning_num_precreate = 'xxx',
   auto_partitioning_num_retention = 'xxx',
   auto_partitioning_num_hot='xxx',
   -- Note: The schd_start_time parameter is supported in Hologres V2.2 and later.
   auto_partitioning_schd_start_time = 'xxx',
   -- Note: The time_format parameter is supported in Hologres V3.0.12 and later.
   auto_partitioning_time_format = 'xxx'
);

Modifique as propriedades de gerenciamento de particionamento dinâmico após criar uma tabela particionada.

-- Modify dynamic partition management properties.
ALTER TABLE [<schema_name>.]<table_name> SET (
   auto_partitioning_enable = 'xxx',
   auto_partitioning_time_unit = 'xxx',
   auto_partitioning_time_zone = 'xxx',
   auto_partitioning_num_precreate = 'xxx',
   auto_partitioning_num_retention = 'xxx',
   auto_partitioning_num_hot='xxx',
   -- Note: The schd_start_time parameter is supported in Hologres V2.2 and later.
   auto_partitioning_schd_start_time = 'xxx',
   -- Note: The time_format parameter is supported in Hologres V3.0.12 and later.
   auto_partitioning_time_format = 'xxx'
);

Exemplo usando a instrução CALL

Configure as propriedades de gerenciamento de particionamento dinâmico ao criar uma tabela particionada.

-- Configure dynamic partition management properties when creating a partitioned table.
BEGIN;
CREATE TABLE [IF NOT EXISTS] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>);
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_hot', 'xxx');
-- Note: The schd_start_time parameter is supported in Hologres V2.2 and later.
CALL set_table_property ('[<schema_name>.]<table_name>', 'auto_partitioning.schd_start_time', 'xxx');
-- Note: The time_format parameter is supported in Hologres V3.0.12 and later.
CALL set_table_property ('[<schema_name>.]<table_name>', 'auto_partitioning.time_format', 'xxx');
COMMIT;

Modifique as propriedades de gerenciamento de particionamento dinâmico após criar uma tabela particionada.

-- Modify dynamic partition management properties.
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
-- Note: The time_format parameter is supported in Hologres V3.0.12 and later.
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_format', 'xxx');

Parâmetros

Importante

Na sintaxe CREATE TABLE WITH, substitua o ponto (.) nos nomes dos parâmetros por um sublinhado (_). Exemplo: auto_partitioning.enable torna-se auto_partitioning_enable.

Nome do parâmetro	Obrigatório	Descrição	Atualizável	Requisitos de versão
`auto_partitioning_enable`/`auto_partitioning.enable`	Não	Ativa ou desativa o gerenciamento de particionamento dinâmico. Valores válidos: true: ativa o gerenciamento de particionamento dinâmico. false (padrão): desativa o gerenciamento de particionamento dinâmico.	Sim	Hologres V1.3 e posteriores
`auto_partitioning_time_unit`/`auto_partitioning.time_unit`	Sim	Granularidade de tempo para rotação de partições. Valores válidos: HOUR DAY MONTH QUARTER YEAR Exemplo: DAY significa que as partições rotacionam diariamente.	Não
`auto_partitioning_time_zone`/`auto_partitioning.time_zone`	Não	Fuso horário para agendamento de partições. O padrão é o fuso horário da conexão atual. Execute a seguinte instrução para listar os fusos horários disponíveis. Use o valor da coluna name como valor de timezone, como Asia/Shanghai. `SELECT * FROM pg_timezone_names;`	Não
`auto_partitioning_num_precreate`/`auto_partitioning.num_precreate`	Não	Número de partições a pré-criar. Valores válidos: 0: sem pré-criação. [1, 512]: cria partições relativas ao horário atual. Padrão: 4. Mínimo recomendado: 2. Nota Exemplo: com `time_unit = DAY, num_precreate = 3` e data atual 2022-01-10, o sistema cria as partições 2022-01-10, 2022-01-11 e 2022-01-12. Importante A pré-criação de partições afeta a função MAX_PT. Verifique se sua carga de trabalho depende de MAX_PT antes de configurar este parâmetro.	Sim
`auto_partitioning_num_retention`/`auto_partitioning.num_retention`	Não	Número de partições históricas a reter. Valores válidos: 0: não retém partições históricas. -1 (padrão): não remove partições históricas. Inteiro positivo: retém N partições históricas. O valor máximo é 512. Para ajustar o máximo de partições retidas (até 8760), execute: `set hg_experimental_auto_part_max_maintained_partitions=<value>;` Nota Exemplo: com `<time_unit = DAY, num_retention = 3>` e data atual 2022-01-10, o sistema retém as partições de 2022-01-09, 2022-01-08 e 2022-01-07 e exclui as mais antigas.	Sim
`auto_partitioning_num_hot`/`auto_partitioning.num_hot`	Não	Número de partições quentes a reter. Valores válidos: 0: não retém partições quentes. -1 (padrão): não remove partições quentes. Inteiro positivo: retém N partições quentes. O valor máximo é 512.		Hologres V1.3.37 e posteriores
`auto_partitioning_schd_start_time`/`auto_partitioning.schd_start_time`	Não	Horário personalizado para agendamento de partições. Se `auto_partitioning.time_unit` for HOUR, o padrão é o início de cada hora. Para outros valores, o padrão é 00:00:01. Formatos de data e hora suportados: Tipos de Data/Hora. Um horário futuro entra em vigor no momento especificado.		Hologres V2.2 e posteriores
`auto_partitioning_time_format`/`auto_partitioning.time_format`	Não	Formato de data e hora para os nomes das chaves de partição ao criar uma tabela. Formatos padrão: YYYYMMDDHH24 (HOUR), YYYYMMDD (DAY), YYYYMM (MONTH), YYYYQ (QUARTER), YYYY (YEAR). Formatos opcionais: YYYY-MM-DD-HH24 (HOUR), YYYY-MM-DD (DAY), YYYY-MM (MONTH), YYYY-Q (QUARTER).	Não	Hologres V3.0.12 e posteriores

Regras de geração de nome de tabela

O particionamento dinâmico nomeia as partições filhas como {parent_table}_{time_suffix}. O sufixo de tempo deriva do valor de auto_partitioning.time_unit e seu modelo de formato:

Unidade de tempo	Formato do sufixo de tempo	Exemplo	Horário de execução
HOUR	YYYYMMDDHH24	2024112221.	Inicia no começo de cada hora. Por exemplo, a tarefa executa às 21:00:01 em 22 de novembro de 2024.
HOUR	YYYY-MM-DD-HH24	2024-11-22-21.
DAY	YYYYMMDD	20241122.	Inicia às 00:00:01 todos os dias. Por exemplo, 00:00:01 em 22 de novembro de 2024.
DAY	YYYY-MM-DD	2024-11-22.
MONTH	YYYYMM	202411.	Inicia às 00:00:01 no primeiro dia de cada mês. Por exemplo, 00:00:01 em 1º de novembro de 2024.
MONTH	YYYY-MM	2024-11.
QUARTER	YYYYQ	20241, 20242, 20243 e 20244 representam os quatro trimestres de 2024.	Inicia às 00:00:01 no primeiro dia de cada trimestre. Por exemplo, 00:00:01 em 1º de janeiro de 2024.
QUARTER	YYYY-Q	2024-1, 2024-2, 2024-3 e 2024-4 representam os quatro trimestres de 2024.
YEAR	YYYY	2023 e 2024 representam partições para os anos de 2023 e 2024.	Inicia às 00:00:01 no primeiro dia de cada ano. Por exemplo, 00:00:01 em 1º de janeiro de 2023.

Exemplos

O exemplo a seguir mostra como definir a unidade de tempo como DAY, pré-criar partições para os próximos 3 dias, reter partições históricas dos últimos 2 dias e definir o fuso horário como Asia/Shanghai.

Exemplo usando a cláusula WITH

Crie a tabela particionada tbl1.

-- In V2.1, create a partitioned table and configure dynamic partition management:

CREATE TABLE tbl1 (
    c1 TEXT NOT NULL,
    c2 TEXT 
)
PARTITION BY LIST (c2)
WITH (
   auto_partitioning_enable = 'true',
   auto_partitioning_time_unit = 'DAY',
   auto_partitioning_time_zone = 'Asia/Shanghai',
   auto_partitioning_num_precreate = '3',
   auto_partitioning_num_retention = '2'
);

Após a geração das partições filhas, insira dados.

INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');

Consulte os dados.

SELECT * FROM tbl1 WHERE c2='20231212';

Resultado retornado:

c1	     c2
Data 1   20231212

Exemplo usando a instrução CALL

Crie a tabela particionada tbl1.

-- Create a partitioned table and configure dynamic partition management:
BEGIN;
CREATE TABLE tbl1 (
    c1 TEXT NOT NULL,
    c2 TEXT 
)
PARTITION BY LIST (c2);
CALL set_table_property ('tbl1', 'auto_partitioning.enable', 'true');
CALL set_table_property ('tbl1', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property ('tbl1', 'auto_partitioning.time_zone', 'Asia/Shanghai');
CALL set_table_property ('tbl1', 'auto_partitioning.num_precreate', '3');
CALL set_table_property ('tbl1', 'auto_partitioning.num_retention', '2');
COMMIT;

Após a geração das partições filhas, insira dados.

INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');

Consulte os dados.

SELECT * FROM tbl1 WHERE c2='20231212';

Resultado retornado:

c1	     c2
Data 1   20231212

Exemplo de ciclo de vida da partição:

Horário	Evento	Resultado
2023-12-12 09:00:00	Execução da instrução SQL anterior para criar a tabela particionada.	Tabela pai criada: tbl1 Partições filhas criadas: tbl1_20231212, tbl1_20231213, tbl1_20231214
2023-12-13 00:00:00	Criação automática de uma partição filha pelo sistema.	Partição filha criada: tbl1_20231215
2023-12-14 00:00:00	Criação automática de uma partição filha pelo sistema.	Partição filha criada: tbl1_20231216
2023-12-15 00:00:00	Criação automática de uma partição filha e remoção de outra pelo sistema.	Partição filha criada: tbl1_20231217 Partição filha removida: tbl1_20231212
2023-12-16 00:00:00	Criação automática de uma partição filha e remoção de outra pelo sistema.	Partição filha criada: tbl1_20231218 Partição filha removida: tbl1_20231213

Cenários comuns

Reter partições filhas específicas

Por padrão, o sistema exclui automaticamente partições filhas fora do intervalo de retenção. Para preservar partições específicas (como dados do festival de compras Double 11 para análise ano a ano), adicione a propriedade keep_alive:

Sintaxe suportada a partir da V2.1:

-- Retain a partition
ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');

-- Stop retaining a partition: After the retention property is removed, dynamic partition management immediately triggers a purge of expired data.
ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');

Sintaxe suportada em todas as versões:

-- Retain a partition
CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');

-- Stop retaining a partition: After the retention property is removed, dynamic partition management immediately triggers a purge of expired data.
CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');

Nota

Substitua table_name pelo nome real da partição filha.

Gerenciar armazenamento em camadas para partições

O armazenamento em camadas equilibra custo e desempenho para tabelas particionadas. Mantenha as N partições mais recentes no hot storage para consultas frequentes, armazene as próximas M partições no cold storage e exclua partições mais antigas que N+M.

Criar uma tabela particionada dinâmica

Exemplo: crie uma partição por dia, mantenha as 7 mais recentes no hot storage, mova as próximas 23 para o cold storage e exclua partições mais antigas.

BEGIN;
CREATE TABLE tbl2(	
  c1 text not null, 
  c2 text
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl2', 'auto_partitioning.enable', 'true');
CALL set_table_property('tbl2', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property('tbl2', 'auto_partitioning.num_precreate', '3');
CALL set_table_property('tbl2', 'auto_partitioning.num_hot', '7');
CALL set_table_property('tbl2', 'auto_partitioning.num_retention', '30');
COMMIT;

A figura a seguir ilustra o efeito:

Effect

Modificar a política de armazenamento

Altere o valor de auto_partitioning.num_hot para ajustar o limite do hot storage. Partições já no cold storage não retornam automaticamente para o hot storage. Suponha que a data atual seja 1º de julho de 2022:

BEGIN;
CREATE TABLE tbl_p(
  c1 text not null,
  c2 text
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl_p', 'auto_partitioning.enable', 'true');
CALL set_table_property('tbl_p', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property('tbl_p', 'auto_partitioning.num_precreate', '3');
CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '3');
CALL set_table_property('tbl_p', 'auto_partitioning.num_retention', '10');
COMMIT;

Dois cenários após modificar a política:

Cenário 1: expandir a política de partições em hot storage
Para aumentar as partições quentes para 4:
```
CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '4');
```
Partições já no cold storage permanecem lá:
Cenário 2: reduzir partições quentes

Para reduzir as partições quentes para 2:
```
CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '2');
```
Partições no cold storage permanecem no cold storage, mas partições quentes elegíveis migram para o cold:

Converter uma tabela de cold storage para particionamento dinâmico

Para converter uma tabela particionada de cold storage em uma tabela particionada dinâmica com os últimos 7 dias em hot storage:

Prepare a tabela.

-- Specify the cold storage medium as the storage policy when creating the table.
BEGIN;
CREATE TABLE tbl2(	
  c1 TEXT NOT NULL, 
  c2 TEXT 
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl2', 'storage_mode', 'cold');

CREATE TABLE tbl2_20230808 PARTITION OF tbl2 FOR VALUES IN('20230808');
CREATE TABLE tbl2_20230809 PARTITION OF tbl2 FOR VALUES IN('20230809');
CREATE TABLE tbl2_20230810 PARTITION OF tbl2 FOR VALUES IN('20230810');
CREATE TABLE tbl2_20230817 PARTITION OF tbl2 FOR VALUES IN('20230817');
COMMIT;

Converta para particionamento dinâmico com 7 dias de hot storage.

BEGIN;
CALL set_table_property('tbl2', 'storage_mode', 'hot'); -- Set the parent table to hot.
CALL set_table_property('tbl2_20230810', 'storage_mode', 'cold'); -- Set partitions that do not need to be moved to hot storage to cold.
CALL set_table_property('tbl2_20230809', 'storage_mode', 'cold');
CALL set_table_property('tbl2_20230808', 'storage_mode', 'cold');
CALL set_table_property('tbl2', 'auto_partitioning.enable', 'true');
CALL set_table_property('tbl2', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property('tbl2', 'auto_partitioning.num_precreate', '3');
CALL set_table_property('tbl2', 'auto_partitioning.num_hot', '7');
CALL set_table_property('tbl2', 'auto_partitioning.num_retention', '10');
COMMIT;

Visualizar configurações e agendamentos de partição

Consulte as configurações de particionamento dinâmico para todas as tabelas no banco de dados atual:

SELECT
    nsp_name AS schema_name,
    tbl_name AS table_name,
    ENABLE,
    time_unit,
    time_zone,
    num_precreate,
    num_retention,
    b.usename AS create_user,
    cret_time,
    schd_start_time,
    options
FROM
    hologres.hg_partitioning_config AS a
    LEFT JOIN pg_user AS b ON a.cret_user = b.usesysid;

Descrições dos campos:

Nome do campo	Descrição
schema_name	Nome do schema.
table_name	Nome da tabela.
ENABLE	Indica se o gerenciamento de particionamento dinâmico está ativado.
time_unit	Unidade de tempo para o particionamento dinâmico.
time_zone	Fuso horário para o particionamento dinâmico.
num_precreate	Número de partições a pré-criar.
num_retention	Número de partições históricas a reter.
create_user	Usuário que criou a configuração.
cret_time	Horário de criação.
schd_start_time	Último horário de execução agendada.

Saída de exemplo:

Visualizar logs de operação de partição

O Query Log não registra a criação e exclusão de partições. Use a seguinte instrução:

SELECT                           
    relname,
    relowner,
    schdtime,
    trigtime,
    status,
    message,
    precreate,
    discard
FROM
    hologres.hg_partitioning_log

Descrições dos campos:

Nome do campo	Descrição
relname	schema.table
relowner	Proprietário da tabela particionada.
schdtime	Horário agendado.
trigtime	Horário real do gatilho.
status	Status.
message	Observações.
precreate	Nome da partição filha criada.
discard	Nome da partição filha removida.

Saída de exemplo:

FAQ

Como ativo o particionamento dinâmico em uma tabela existente?

Execute as seguintes instruções:

-- SQL example for Hologres V2.1 and later
ALTER TABLE auto_part_old SET (
   auto_partitioning_enable = 'true',
   auto_partitioning_time_unit = 'HOUR',
   auto_partitioning_time_zone = 'PRC',
   auto_partitioning_num_precreate = '4',
   auto_partitioning_num_retention = '-1',
   auto_partitioning_num_hot = '-1'
);

-- SQL example for all Hologres versions
BEGIN;
CALL set_table_property('auto_part_old', 'auto_partitioning.enable', 'true');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_unit', 'HOUR');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_zone', 'PRC');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_precreate', '4');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_retention', '-1');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_hot', '-1');
COMMIT;

Importante

auto_partitioning.time_unit e auto_partitioning.time_zone só podem ser definidos uma vez após atualizar o Hologres. Eles não podem ser alterados após a configuração.

As partições filhas históricas são afetadas após ativar o particionamento dinâmico em uma tabela existente?

O sistema remove partições filhas com base em seus nomes. Apenas partições que correspondem à convenção de nomenclatura {parent_table}_{time_suffix} são removidas.

Por que as partições filhas não são criadas imediatamente após definir num_precreate?

A tarefa inicial de criação de partições executa em um ciclo de verificação de 10 minutos. As partições filhas aparecem dentro de 10 minutos.