Use partitioned OSS foreign tables - AnalyticDB: Central de Documentação do Alibaba Cloud

AnalyticDB for PostgreSQL oferece suporte a tabelas externas particionadas do OSS (OSS FDW). Quando uma coluna de partição é incluída na cláusula WHERE de uma consulta, esse recurso reduz significativamente o volume de dados verificados no OSS e melhora o desempenho da consulta.

Observações de uso

O recurso de tabela externa particionada do OSS exige que os objetos no OSS sigam uma estrutura de diretórios específica. O caminho do diretório deve estar no formato oss://bucket/partcol1=partval1/partcol2=partval2/, em que partcol1 e partcol2 são colunas de partição, e partval1 e partval2 são os valores correspondentes à partição.

Por exemplo, se uma tabela for particionada pela coluna year e subparticionada pela coluna month, os objetos de uma partição específica, como year=2022 e month=07, devem ser armazenados no diretório oss://bucket/year=2022/month=07/.

Crie um servidor OSS, mapeamento de usuário OSS e OSS FDW

Antes de usar o OSS FDW, crie um servidor OSS, um mapeamento de usuário OSS e o OSS FDW.

Para obter informações sobre como criar um servidor OSS, consulte Criar um servidor OSS.
Para obter detalhes sobre a criação de um mapeamento de usuário OSS, visualize Criar um mapeamento de usuário OSS.
Para saber mais sobre como criar um OSS FDW, acesse Criar uma tabela externa do OSS.

Crie uma tabela particionada

Use a instrução CREATE FOREIGN TABLE para criar uma tabela externa particionada do OSS. A sintaxe é a mesma usada para criar uma tabela particionada padrão. Para mais informações, consulte Definir tabelas particionadas.

Para mais detalhes sobre a sintaxe de CREATE FOREIGN TABLE, visualize Criar uma tabela externa do OSS.

Nota

Atualmente, as tabelas externas do OSS suportam apenas particionamento por lista.

Crie uma tabela particionada chamada ossfdw_parttable com um modelo de partição:

CREATE FOREIGN TABLE ossfdw_parttable( 
  key text,
  value bigint,
  pt text, -- Partition key
  region text -- Subpartition key
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt) -- Partition the table by the pt column.
SUBPARTITION BY LIST (region) -- Subpartition the table by the region column.
    SUBPARTITION TEMPLATE ( -- Subpartition template
       SUBPARTITION hangzhou VALUES ('hangzhou'),
       SUBPARTITION shanghai VALUES ('shanghai')
    )
( PARTITION "20170601" VALUES ('20170601'), 
  PARTITION "20170602" VALUES ('20170602'));

Crie uma tabela particionada chamada ossfdw_parttable1 sem um modelo de partição:

CREATE FOREIGN TABLE ossfdw_parttable1( 
  key text,
  value bigint,
  pt text, -- Partition key
  region text -- Subpartition key
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt) -- Partition the table by the pt column.
SUBPARTITION BY LIST (region) -- Subpartition the table by the region column.
(
    -- The following two partitions can contain different subpartitions.
    VALUES('20181218')
    (
        VALUES('hangzhou'),
        VALUES('shanghai') 
    ),
    VALUES('20181219')
    (
        VALUES('nantong'),
        VALUES('anhui') 
    ) 
);

Modifique uma tabela externa particionada do OSS

Use a instrução ALTER TABLE para modificar uma tabela externa particionada do OSS. O AnalyticDB for PostgreSQL permite adicionar e remover partições em uma tabela externa particionada do OSS.

Adicionar uma partição

Adicionar uma partição
- Para adicionar uma partição à tabela ossfdw_parttable, execute a seguinte instrução. Como essa tabela utiliza um modelo de partição, o sistema cria automaticamente as subpartições correspondentes.
```
ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');
```
  O esquema é atualizado da seguinte forma:
- Para adicionar uma partição à tabela ossfdw_parttable1, execute a instrução abaixo. Como esta tabela não usa um modelo de partição, especifique explicitamente as subpartições.
```
ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
(
    VALUES('hefei'),
    VALUES('guangzhou') 
);
```
Adicionar uma subpartição

Para incluir uma subpartição na partição 20170603 da tabela ossfdw_parttable, execute o comando a seguir:
```
ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');
```
O esquema é atualizado conforme mostrado abaixo:

Remover uma partição

Para remover uma partição, execute a seguinte instrução:

ALTER TABLE ossfdw_parttable DROP PARTITION FOR ('20170601');

Para excluir uma subpartição, utilize o comando abaixo:

ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170602') DROP PARTITION FOR ('hangzhou');

Excluir uma tabela particionada

Utilize a instrução DROP FOREIGN TABLE para excluir uma tabela particionada.

Exemplo:

DROP FOREIGN TABLE ossfdw_parttable;

Acessar dados do SLS com uma tabela externa do OSS

O acesso a dados enviados do Log Service (SLS) é um caso de uso comum para tabelas externas particionadas do OSS. Se o SLS gravar dados no OSS usando uma estrutura de diretórios compatível, defina uma tabela particionada para consultar esses dados.

Para mais informações sobre o Log Service (SLS), consulte O que é o Log Service.

Criar uma tarefa de envio para o OSS (versão antiga).
No painel OSS LogShipper, ao configurar a tarefa de envio, recomendamos definir o campo Partition format como date=%Y%m/userlogin. Um exemplo da estrutura de diretórios gerada no OSS é mostrado abaixo:
```
oss://testBucketName/adbpgossfdw
├── date=202002
│ ├── userlogin_158561762910654****_647504382.csv
│ └── userlogin_158561784923220****_647507440.csv
└── date=202003
    └── userlogin_158561794424704****_647508762.csv
```

Crie uma tabela externa particionada do OSS com um esquema que corresponda aos dados de log do SLS:

CREATE FOREIGN TABLE userlogin (
        uid integer,
        name character varying,
        source integer,
        logindate timestamp without time zone,
        "date" int
) SERVER oss_serv OPTIONS (
    dir 'adbpgossfdw/',
    format 'text'
)
PARTITION BY LIST ("date")
(
        VALUES ('202002'), 
        VALUES ('202003') 
)

Visualize o plano de execução de uma consulta nos dados de log.

Por exemplo, para analisar o número de logins de usuários em fevereiro de 2020, execute a seguinte instrução:

EXPLAIN SELECT uid, count(uid) FROM userlogin WHERE "date" = 202002 GROUP BY uid;

A seguinte saída é retornada:

 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1 (slice2; segments: 3) (cost=5135.10..5145.10 rows=1000 width=12)
   -> HashAggregate (cost=5135.10..5145.10 rows=334 width=12)
         Group Key: userlogin_1_prt_1.uid
         -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=5100.10..5120.10 rows=334 width=12)
               Hash Key: userlogin_1_prt_1.uid
               -> HashAggregate (cost=5100.10..5100.10 rows=334 width=12)
                     Group Key: userlogin_1_prt_1.uid
                     ->t; Append (cost=0.00..100.10 rows=333334 width=4)
                           -> Foreign Scan on userlogin_1_prt_1 (cost=0.00..100.10 rows=333334 width=4)
                                 Filter: (date = 202002)
                                 Oss Url: endpoint=oss-cn-hangzhou-zmf-internal.aliyuncs.com bucket=adbpg-regress dir=adbpgossfdw/date=202002/ filetype=plain|text
                                 Oss Parallel (Max 4) Get: total 0 file(s) with 0 bytes byte(s).
 Optimizer: Postgres query optimizer
(13 rows)

O plano de execução mostra que a consulta verifica apenas objetos do diretório date=202002 no OSS. Verificar menos dados melhora significativamente o desempenho da consulta.