Utilisez les tables externes OSS pour importer et analyser des données depuis Object Storage Service (OSS). Basées sur le wrapper de données étrangères (FDW) pour OSS, ces tables prennent également en charge l'importation de données entre comptes.
Limites
L'instance AnalyticDB for PostgreSQL et le bucket OSS doivent se trouver dans la même région.
Fonctionnalités
OSS FDW repose sur le framework Foreign Data Wrapper (FDW) de PostgreSQL. Il vous permet d’effectuer les opérations suivantes :
Importer des données depuis OSS vers des tables locales orientées ligne ou colonne afin d’accélérer l’analyse.
Interroger et analyser directement de grands volumes de données stockés dans OSS.
Joindre des tables externes OSS avec des tables locales pour l’analyse.
OSS FDW prend en charge divers formats de fichiers de données, notamment :
Fichiers texte non compressés aux formats CSV, TEXT, JSON et JSON Lines.
Fichiers texte aux formats CSV et TEXT, compressés avec GZIP ou Snappy.
Fichiers texte aux formats JSON et JSON Lines, compressés avec GZIP.
Fichiers binaires ORC. Pour les correspondances de types de données entre ORC et AnalyticDB for PostgreSQL, consultez Correspondances de types de données pour les fichiers ORC.
Fichiers binaires Parquet. Pour les correspondances de types de données entre Parquet et AnalyticDB for PostgreSQL, consultez Correspondances de types de données pour les fichiers Parquet.
Fichiers binaires Avro. Pour les correspondances de types de données entre Avro et AnalyticDB for PostgreSQL, consultez Correspondances de types de données pour les fichiers Avro.
Avant de commencer
Préparer les données OSS
Préparez le fichier exemple example.csv.
Trouver les informations du bucket OSS
Cette section explique comment trouver les éléments Buckets, Bucket Name, Port et Bucket Domain Name.
Connectez-vous à la console OSS.
Dans le volet de navigation de gauche, cliquez sur Access from ECS over the VPC (internal network).
-
Sur la page Endpoint, cliquez sur le bucket cible.
Sur la page Object Management, vous pouvez trouver le nom du bucket.
Sur la page Object Management, repérez le chemin de l’objet.
Dans le volet de navigation de gauche, cliquez sur Overview.
-
Dans la section Port de la page Overview, repérez l’endpoint et le nom de domaine du bucket.
Nous vous recommandons d’utiliser l’endpoint pour Access from ECS over the VPC (internal network).
Obtenir un ID de clé AccessKey et une clé secrète AccessKey
Pour obtenir un ID de clé AccessKey et une clé secrète AccessKey, consultez Créer une clé AccessKey.
Création d'un serveur OSS
Utilisez l'instruction CREATE SERVER pour créer un serveur OSS. Ce serveur définit la connexion au service OSS auquel vous souhaitez accéder. Pour plus d'informations sur l'instruction CREATE SERVER, consultez la documentation CREATE SERVER.
Syntaxe
CREATE SERVER server_name
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]
Paramètres
|
Paramètre |
Type |
Obligatoire |
Description |
|
server_name |
STRING |
Oui |
Nom du serveur OSS. |
|
fdw_name |
STRING |
Oui |
Nom du wrapper de données externes qui gère le serveur. Ce paramètre doit être défini sur oss_fdw. |
Le tableau suivant détaille les paramètres de la clause OPTIONS.
|
Paramètre |
Type |
Obligatoire |
Description |
|
endpoint |
STRING |
Oui |
Endpoint utilisé pour accéder à OSS. AnalyticDB for PostgreSQL prend uniquement en charge les endpoints internes. Pour plus d'informations, consultez la section relative au cloud public dans Régions et endpoints. |
|
bucket |
STRING |
Non |
Nom du bucket où sont stockés les fichiers de données. Pour savoir comment obtenir le nom du bucket, consultez la section Préparatifs. Remarque
|
|
speed_limit |
NUMERIC |
Non |
Quantité minimale de données à transférer pendant la période spécifiée par speed_time afin d'éviter un délai d'attente. Unité : octets. La valeur par défaut est 1024. Utilisez cette option conjointement avec l'option speed_time. Remarque
Par défaut, un délai d'attente se déclenche si moins de 1024 octets de données sont transférés pendant 90 secondes consécutives. Pour plus d'informations, consultez la documentation Gestion des erreurs du SDK OSS. |
|
speed_time |
NUMERIC |
Non |
Période durant laquelle la quantité minimale de transfert de données spécifiée par speed_limit doit être atteinte. Unité : secondes. La valeur par défaut est 90. Utilisez cette option conjointement avec l'option speed_limit. Remarque
Par défaut, un délai d'attente se déclenche si moins de 1024 octets de données sont transférés pendant 90 secondes consécutives. Pour plus d'informations, consultez la documentation Gestion des erreurs du SDK OSS. |
|
connect_timeout |
NUMERIC |
Non |
Délai d'attente de la connexion. Unité : secondes. La valeur par défaut est 10. |
|
dns_cache_timeout |
NUMERIC |
Non |
Délai d'expiration du cache DNS. Unité : secondes. La valeur par défaut est 60. |
Exemples
CREATE SERVER oss_serv
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
endpoint 'oss-cn-********.aliyuncs.com',
bucket 'adb-pg'
);
Vous pouvez également utiliser l'instruction ALTER SERVER pour modifier la configuration du serveur OSS. Pour plus d'informations, consultez la documentation ALTER SERVER.
Exemples :
-
Modification d'une option du serveur OSS.
ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com'); -
Ajout d'une option au serveur OSS.
ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20'); -
Suppression d'une option du serveur OSS.
ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);
Pour supprimer le serveur OSS, utilisez l'instruction DROP SERVER. Pour plus d'informations, consultez la documentation DROP SERVER.
Créer un mappage d'utilisateur OSS
Après avoir créé un serveur OSS, vous devez également créer un mappage d'utilisateur. L'instruction CREATE USER MAPPING définit une correspondance entre un utilisateur de votre base de données AnalyticDB for PostgreSQL et les identifiants permettant d'accéder au serveur OSS. Pour plus d'informations, consultez la documentation CREATE USER MAPPING.
Syntaxe
CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
SERVER <server_name>
[ OPTIONS ( option 'value' [, ... ] ) ]
Paramètres
|
Paramètre |
Type |
Obligatoire |
Description |
|
username |
string |
Oui. Vous devez spécifier l'une des quatre options. |
Le nom d'un utilisateur existant à mapper dans l'instance AnalyticDB for PostgreSQL. |
|
USER |
string |
Mappe l'utilisateur actuel de l'instance AnalyticDB for PostgreSQL. |
|
|
CURRENT_USER |
string |
||
|
PUBLIC |
string |
Crée un mappage public qui s'applique à tous les utilisateurs de l'instance AnalyticDB for PostgreSQL, y compris ceux créés ultérieurement. |
|
|
server_name |
string |
Oui |
Le nom du serveur OSS. |
Le tableau suivant décrit les paramètres de la clause OPTIONS.
|
Paramètre |
Type |
Obligatoire |
Description |
|
id |
string |
Oui |
L'AccessKey ID. Pour en obtenir un, consultez la section Créer une paire AccessKey. |
|
key |
string |
Oui |
Le secret AccessKey. Pour en obtenir un, consultez la section Créer une paire AccessKey. |
Lorsque vous importez ou exportez des données entre différents comptes Alibaba Cloud, vous devez configurer l'AccessKey ID et l'AccessKey Secret du compte Alibaba Cloud propriétaire du compartiment OSS.
Exemple
CREATE USER MAPPING FOR PUBLIC
SERVER oss_serv
OPTIONS (
id 'LTAI****************',
key 'yourAccessKeySecret'
);
Vous pouvez également utiliser l'instruction DROP USER MAPPING pour supprimer un mappage d'utilisateur. Pour plus d'informations, consultez la documentation DROP USER MAPPING.
Créer une table externe OSS
Une fois que vous disposez d'un serveur OSS et d'un utilisateur autorisé à y accéder, utilisez l'instruction CREATE FOREIGN TABLE pour créer une table externe OSS. Pour plus d'informations, consultez la documentation CREATE FOREIGN TABLE.
Syntaxe
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
Paramètres
|
Paramètre |
Type |
Obligatoire |
description |
|
table_name |
string |
Oui |
Nom de la table externe OSS. |
|
column_name |
string |
Oui |
Nom de la colonne. |
|
data_type |
string |
Oui |
Type de données de la colonne. |
Le tableau suivant répertorie les paramètres de la clause OPTIONS.
|
Paramètre |
Type |
Obligatoire |
Description |
|
filepath |
string |
Oui. L'un de ces trois paramètres est requis. |
Le chemin d'accès complet et le nom d'un seul objet dans OSS. Si vous utilisez ce paramètre, seul l'objet spécifié est sélectionné. |
|
prefix |
string |
Spécifie un préfixe pour les chemins d'accès des objets. Seuls les objets dont le chemin commence par ce préfixe sont sélectionnés. Les expressions régulières ne sont pas prises en charge. Exemples :
|
|
|
dir |
string |
Le chemin du répertoire dans OSS doit se terminer par /, par exemple test/mydir/. Sélectionne tous les objets du répertoire spécifié, à l'exclusion des objets contenus dans ses sous-répertoires. |
|
|
bucket |
string |
Non |
Le nom du bucket contenant les objets de données. Consultez la section Préparatifs pour savoir comment obtenir le nom du bucket. Remarque
|
|
format |
string |
Oui |
Le format de l'objet. Valeurs possibles :
|
|
filetype |
string |
Non |
Le type de compression de l'objet. Valeurs possibles :
Remarque
|
|
log_errors |
boolean |
Non |
Indique si les erreurs doivent être enregistrées dans un fichier journal. La valeur par défaut est Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
segment_reject_limit |
numeric |
Non |
Le seuil d'erreur qui entraîne l'abandon de la tâche de chargement des données. Si la valeur inclut un signe pourcentage (
Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
header |
boolean |
Non |
Indique si l'objet source contient une ligne d'en-tête. Valeurs possibles :
Remarque
Ce paramètre s'applique uniquement aux objets au format csv. |
|
delimiter |
string |
Non |
Le caractère délimiteur qui sépare les champs. Il doit s'agir d'un caractère codé sur un seul octet.
Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
quote |
string |
Non |
Le caractère de guillemet pour les champs. Il doit s'agir d'un caractère codé sur un seul octet. La valeur par défaut est le guillemet double ( Remarque
Ce paramètre s'applique uniquement aux objets au format csv. |
|
escape |
string |
Non |
Caractère d'échappement précédant un caractère ayant une signification spéciale. Il doit s'agir d'un caractère codé sur un seul octet. La valeur par défaut est le guillemet double ( Remarque
Ce paramètre s'applique uniquement aux objets au format csv. |
|
null |
string |
Non |
Spécifie la chaîne représentant une valeur NULL dans l'objet de données.
Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
encoding |
string |
Non |
Spécifie l'encodage des objets de données. La valeur par défaut correspond à l'encodage du client. Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
force_not_null |
boolean |
Non |
Indique si les valeurs de champ peuvent être des chaînes vides. Valeurs possibles :
Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
|
force_null |
boolean |
Non |
Définit la manière dont les chaînes vides sont traitées. Valeurs possibles :
Remarque
Ce paramètre s'applique uniquement aux objets aux formats csv et text. |
Exemple
CREATE FOREIGN TABLE ossexample (
date text,
time text,
open float,
high float,
low float,
volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');
Une fois que vous avez créé une table externe OSS, utilisez l'une des méthodes suivantes pour vérifier les objets OSS auxquels la table est mappée :
-
Méthode 1 :
EXPLAIN VERBOSE SELECT * FROM <OSS foreign table name>; -
Méthode 2 :
SELECT * FROM get_oss_table_meta('<OSS foreign table name>');
Vous pouvez également utiliser l'instruction DROP FOREIGN TABLE pour supprimer la table externe OSS. Pour plus d'informations, consultez la page DROP FOREIGN TABLE.
Interrogation et analyse des données OSS
Vous pouvez interroger une table externe OSS de la même manière qu'une table locale. Les exemples suivants illustrent des requêtes courantes.
-
Interrogez les données à l'aide d'un filtre clé-valeur.
SELECT * FROM ossexample WHERE volume = 5; -
Interrogez les données à l'aide d'une fonction d'agrégation.
SELECT count(*) FROM ossexample WHERE volume = 5; -
Interrogez les données en utilisant les clauses GROUP BY et LIMIT.
SELECT low, sum(volume) FROM ossexample GROUP BY low ORDER BY low limit 5;
Analyse de jointure avec des tables externes OSS
-
Créez une table locale nommée example et insérez des données de test.
CREATE TABLE example (id int, volume int); INSERT INTO example VALUES(1,1), (2,3), (4,5); -
Exécutez une requête de jointure sur les tables example et ossexample.
SELECT example.volume, min(high), max(low) FROM ossexample, example WHERE ossexample.volume = example.volume GROUP BY(example.volume) ORDER BY example.volume;
Tolérance aux pannes
OSS FDW offre une tolérance aux pannes grâce aux paramètres log_errors et segment_reject_limit. Cela empêche les erreurs présentes dans les données brutes d'interrompre l'analyse d'une table externe OSS.
Pour plus d'informations sur les paramètres log_errors et segment_reject_limit, consultez la section Créer une table externe OSS.
-
Créez une table externe OSS tolérante aux pannes.
CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text) SERVER oss_serv OPTIONS (log_errors 'true', -- Enable error logging. segment_reject_limit '10', -- Set the reject limit to 10 rows. The scan is aborted if this limit is exceeded. dir 'error_sales/', -- Specify the OSS object directory for the foreign table. format 'csv', -- Specify the object format as CSV. encoding 'utf8'); -- Specify the encoding. -
Interrogez le journal des erreurs.
SELECT * FROM gp_read_error_log('oss_error_sales'); -
Effacez le journal des erreurs.
SELECT gp_truncate_error_log('oss_error_sales');
FAQ
Q : La suppression de données d'une table externe OSS entraîne-t-elle également la suppression des données sous-jacentes dans OSS ?
R : Non. La suppression de données d'une table externe OSS ne supprime pas les données sous-jacentes dans OSS.