Use partitioned OSS foreign tables - AnalyticDB - Centro de documentación de Alibaba Cloud

AnalyticDB for PostgreSQL admite tablas externas particionadas de OSS (OSS FDW). Cuando se incluye una columna de partición en la cláusula WHERE de una consulta, esta función reduce significativamente la cantidad de datos escaneados desde OSS y mejora el rendimiento de las consultas.

Notas de uso

La función de tabla externa particionada de OSS requiere que los objetos en OSS sigan una estructura de directorios específica. La ruta del directorio debe tener el formato oss://bucket/partcol1=partval1/partcol2=partval2/, donde partcol1 y partcol2 son columnas de partición, y partval1 y partval2 son los valores correspondientes para la partición.

Por ejemplo, si una tabla está particionada por la columna year y subparticionada por la columna month, los objetos de una partición específica, como year=2022 y month=07, deben almacenarse en el directorio oss://bucket/year=2022/month=07/.

Creación de un servidor OSS, asignación de usuario OSS y OSS FDW

Antes de utilizar OSS FDW, debe crear previamente un servidor OSS, una asignación de usuario OSS y un OSS FDW.

Para obtener información sobre cómo crear un servidor OSS, consulte Create an OSS server.
Para obtener información sobre cómo crear una asignación de usuario OSS, consulte Create an OSS user mapping.
Para obtener información sobre cómo crear un OSS FDW, consulte Create an OSS foreign table.

Creación de una tabla particionada

Utilice la instrucción CREATE FOREIGN TABLE para crear una tabla externa particionada de OSS. La sintaxis es la misma que se usa para crear una tabla particionada estándar. Para más información, consulte Defining partitioned tables.

Para más información sobre la sintaxis de CREATE FOREIGN TABLE, consulte Create an OSS foreign table.

Nota

Actualmente, las tablas externas de OSS solo admiten particionamiento por lista.

Cree una tabla particionada llamada ossfdw_parttable con una plantilla de partición:

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

Cree una tabla particionada llamada ossfdw_parttable1 sin una plantilla de partición:

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

Modificación de una tabla externa particionada de OSS

Utilice la instrucción ALTER TABLE para modificar una tabla externa particionada de OSS. AnalyticDB for PostgreSQL permite agregar y eliminar particiones en una tabla externa particionada de OSS.

Agregar una partición

Agregar una partición
- Para agregar una partición a la tabla ossfdw_parttable, ejecute la siguiente instrucción. Dado que esta tabla utiliza una plantilla de partición, el sistema crea automáticamente las subparticiones correspondientes.
```
ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');
```
  El esquema se actualiza de la siguiente manera:
- Para agregar una partición a la tabla ossfdw_parttable1, ejecute la siguiente instrucción. Como esta tabla no utiliza una plantilla de partición, debe especificar explícitamente las subparticiones.
```
ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
(
    VALUES('hefei'),
    VALUES('guangzhou') 
);
```
Agregar una subpartición

Para agregar una subpartición a la partición 20170603 de la tabla ossfdw_parttable, ejecute la siguiente instrucción:
```
ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');
```
El esquema se actualiza de la siguiente manera:

Eliminar una partición

Para eliminar una partición, ejecute la siguiente instrucción:
```
ALTER TABLE ossfdw_parttable DROP PARTITION FOR ('20170601');
```

Para eliminar una subpartición, ejecute la siguiente instrucción:

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

Eliminación de una tabla particionada

Utilice la instrucción DROP FOREIGN TABLE para eliminar una tabla particionada.

Ejemplo:

DROP FOREIGN TABLE ossfdw_parttable;

Acceso a datos de SLS con una tabla externa de OSS

El acceso a datos enviados desde Log Service (SLS) es un caso de uso común para las tablas externas particionadas de OSS. Si SLS escribe datos en OSS mediante una estructura de directorios compatible, puede definir una tabla particionada para consultar dichos datos.

Para más información sobre Log Service (SLS), consulte What is Log Service.

Create an OSS shipping task (old version).
En el panel OSS LogShipper, al configurar la tarea de envío, se recomienda establecer Partition format en date=%Y%m/userlogin. A continuación se muestra un ejemplo de la estructura de directorios de OSS generada:
```
oss://testBucketName/adbpgossfdw
├── date=202002
│ ├── userlogin_158561762910654****_647504382.csv
│ └── userlogin_158561784923220****_647507440.csv
└── date=202003
    └── userlogin_158561794424704****_647508762.csv
```

Cree una tabla externa particionada de OSS con un esquema que coincida con los datos de registro de 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') 
)

Consulte el plan de ejecución de una consulta sobre los datos de registro.

Por ejemplo, para analizar la cantidad de inicios de sesión de usuarios en febrero de 2020, ejecute la siguiente instrucción:

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

Se devuelve el siguiente resultado:

 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)

El plan de ejecución muestra que la consulta solo escanea objetos del directorio date=202002 en OSS. Escanear menos datos mejora significativamente el rendimiento de la consulta.