AnalyticDB for PostgreSQL prend en charge les tables externes OSS partitionnées (OSS FDW). Lorsqu'une colonne de partition figure dans la clause WHERE d'une requête, cette fonctionnalité réduit considérablement le volume de données analysées depuis OSS, ce qui améliore les performances des requêtes.
Notes d'utilisation
Les tables externes OSS partitionnées exigent que les objets stockés dans OSS respectent une structure de répertoire spécifique. Le chemin du répertoire doit suivre le format oss://bucket/partcol1=partval1/partcol2=partval2/, où partcol1 et partcol2 représentent les colonnes de partition, et partval1 et partval2 correspondent aux valeurs de partition associées.
Par exemple, si une table partitionnée utilise la colonne year pour le partitionnement principal et la colonne month pour le sous-partitionnement, les objets d'une partition donnée, telle que year=2022 et month=07, doivent être stockés dans le répertoire oss://bucket/year=2022/month=07/.
Créer un serveur OSS, un mappage utilisateur OSS et un OSS FDW
Avant d'utiliser OSS FDW, créez préalablement un serveur OSS, un mappage utilisateur OSS et un OSS FDW.
Pour savoir comment créer un serveur OSS, consultez la section Créer un serveur OSS.
Pour savoir comment créer un mappage utilisateur OSS, consultez la section Créer un mappage utilisateur OSS.
Pour savoir comment créer un OSS FDW, consultez la section Créer une table externe OSS.
Créer une table partitionnée
Utilisez l'instruction CREATE FOREIGN TABLE pour créer une table externe OSS partitionnée. La syntaxe est identique à celle utilisée pour créer une table partitionnée standard. Pour plus d'informations, consultez la rubrique Définition des tables partitionnées.
Pour plus de détails sur la syntaxe de l'instruction CREATE FOREIGN TABLE , reportez-vous à la section Créer une table externe OSS.
Les tables externes OSS ne prennent actuellement en charge que le partitionnement par liste.
-
Créez une table partitionnée nommée
ossfdw_parttableavec un modèle de partition :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')); -
Créez une table partitionnée nommée
ossfdw_parttable1sans modèle de partition :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') ) );
Modifier une table externe OSS partitionnée
Utilisez l'instruction ALTER TABLE pour modifier une table externe OSS partitionnée. AnalyticDB for PostgreSQL permet d'ajouter et de supprimer des partitions sur une table externe OSS partitionnée.
Ajouter une partition
-
Ajouter une partition
-
Pour ajouter une partition à la table
ossfdw_parttable, exécutez l'instruction suivante. Étant donné que cette table utilise un modèle de partition, le système crée automatiquement les sous-partitions correspondantes.ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');Le schéma est mis à jour comme suit :

-
Pour ajouter une partition à la table
ossfdw_parttable1, exécutez l'instruction suivante. Comme cette table n'utilise pas de modèle de partition, vous devez spécifier explicitement les sous-partitions.ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220') ( VALUES('hefei'), VALUES('guangzhou') );
-
-
Ajouter une sous-partition
Pour ajouter une sous-partition à la partition
20170603de la tableossfdw_parttable, exécutez l'instruction suivante :ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');Le schéma est mis à jour comme suit :

Supprimer une partition
-
Pour supprimer une partition, exécutez l'instruction suivante :
ALTER TABLE ossfdw_parttable DROP PARTITION FOR ('20170601'); -
Pour supprimer une sous-partition, exécutez l'instruction suivante :
ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170602') DROP PARTITION FOR ('hangzhou');
Supprimer une table partitionnée
Utilisez l'instruction DROP FOREIGN TABLE pour supprimer une table partitionnée.
Exemple :
DROP FOREIGN TABLE ossfdw_parttable;
Accéder aux données SLS avec une table externe OSS
L'accès aux données exportées depuis Log Service (SLS) constitue un cas d'utilisation courant des tables externes OSS partitionnées. Si SLS écrit les données dans OSS en respectant une structure de répertoire compatible, vous pouvez définir une table partitionnée pour interroger ces données.
Pour plus d'informations sur Log Service (SLS), consultez la rubrique Présentation de Log Service.
-
Créer une tâche d'exportation OSS (ancienne version).
Dans le panneau OSS LogShipper, lors de la configuration de la tâche d'exportation, nous vous recommandons de définir Partition format sur
date=%Y%m/userlogin. Voici un exemple de la structure de répertoire OSS générée :oss://testBucketName/adbpgossfdw ├── date=202002 │ ├── userlogin_158561762910654****_647504382.csv │ └── userlogin_158561784923220****_647507440.csv └── date=202003 └── userlogin_158561794424704****_647508762.csv -
Créez une table externe OSS partitionnée dont le schéma correspond aux données de journal 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') ) -
Consultez le plan d'exécution d'une requête sur les données de journal.
Par exemple, pour analyser le nombre de connexions utilisateur en février 2020, exécutez l'instruction suivante :
EXPLAIN SELECT uid, count(uid) FROM userlogin WHERE "date" = 202002 GROUP BY uid;Le résultat suivant s'affiche :
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)Le plan d'exécution indique que la requête analyse uniquement les objets du répertoire date=202002 dans OSS. La réduction du volume de données analysées améliore considérablement les performances de la requête.