Interroger des données externes avec Autonomous Database sur une infrastructure Exadata dédiée

Décrit les ensembles et les outils pour interroger et valider les données avec Autonomous Database sur une infrastructure Exadata dédiée.

Les données externes ne sont pas gérées par la base de données; toutefois, vous pouvez utiliser les procédures DBMS_CLOUD pour interroger vos données externes. Bien que les interrogations sur des données externes ne soient pas aussi rapides que les interrogations sur des tables de base de données, vous pouvez utiliser cette approche pour commencer rapidement à exécuter des interrogations sur vos fichiers sources externes et vos données externes.

Vous pouvez utiliser les procédures DBMS_CLOUD pour valider les données des fichiers sources externes d'une table externe afin de pouvoir identifier les problèmes et corriger les données dans la table externe ou exclure les données non valides avant d'utiliser les données.

Note :

Si vous n'utilisez pas l'utilisateur ADMIN, assurez-vous que l'utilisateur dispose des privilèges nécessaires pour les opérations que l'utilisateur doit effectuer. Pour plus d'informations, voir Gérer les privilèges d'utilisateur de base de données.

Interroger des données externes

Pour interroger les données de fichiers en nuage, vous devez d'abord stocker les données d'identification du stockage d'objets dans votre base de données autonome, puis créer une table externe à l'aide de la procédure PL/SQL DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Le fichier source de cet exemple, channels.txt, contient les données suivantes : 

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
  1. Stockez vos données d'identification du service de stockage d'objets en nuage à l'aide de la procédure DBMS_CREDENTIAL.CREATE_CREDENTIAL. Par exemple : 
    BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@oracle.com',
    password => 'password'
  );
END;
/

    Les valeurs que vous indiquez pour username et password dépendent du service de stockage d'objets en nuage que vous utilisez :

    • Oracle Cloud Infrastructure Object Storage : username est votre nom d'utilisateur Oracle Cloud Infrastructure et password est votre jeton d'authentification Oracle Cloud Infrastructure. Voir Utilisation de jetons d'authentification.

    • Oracle Cloud Infrastructure Object Storage Classic : username est votre nom d'utilisateur Oracle Cloud Infrastructure version classique et password est votre mot de passe Oracle Cloud Infrastructure version classique.

    Cette opération stocke les données d'identification dans la base de données dans un format chiffré. Vous pouvez utiliser n'importe quel nom pour les données d'identification. Notez que cette étape n'est requise qu'une seule fois, sauf si les données d'identification du magasin d'objets changent. Une fois que vous avez stocké les données d'identification, vous pouvez utiliser le même nom pour tous les chargements de données.

  2. Créez une table externe au-dessus de vos fichiers sources à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

    La procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE prend en charge les fichiers externes dans les services de stockage d'objets en nuage pris en charge. Les informations d'identification sont une propriété de niveau table. Par conséquent, les fichiers externes doivent se trouver dans le même magasin d'objets.

    Par exemple :

    BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'CHANNELS_EXT',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/channels.txt',
    format => json_object('delimiter' value ','),
    column_list => 'CHANNEL_ID NUMBER, 
	CHANNEL_DESC VARCHAR2(20), 
	CHANNEL_CLASS VARCHAR2(20)'
 );
END;
/

    Les paramètres sont les suivants :

    • table_name : Nom de la table externe.

    • credential_name : Nom des données d'identification créées à l'étape précédente.

    • file_uri_list : Liste délimitée par des virgules des fichiers sources à interroger.

      Dans cet exemple, file_uri_list est un URI Swift d'Oracle Cloud Infrastructure qui spécifie le fichier channels.txt dans le seau mybucket de la région us-phoenix-1 (idthydc0kinr est l'espace de noms du stockage d'objets dans lequel réside le seau.) Pour plus d'informations sur les formats d'URI pris en charge, voir Formats d'URI du service de stockage d'objets en nuage.

    • format : Définit les options que vous pouvez spécifier pour décrire le format du fichier source. Pour plus d'informations sur les options de format que vous pouvez spécifier, voir Paramètre de format.

    • column_list : Liste délimitée par des virgules des définitions de colonne dans les fichiers sources.

    Vous pouvez maintenant exécuter des interrogations sur la table externe que vous avez créée à l'étape précédente. Par exemple :

    SELECT count(*) FROM channels_ext;

    Par défaut, la base de données s'attend à ce que toutes les rangées du fichier de données externe soient valides et correspondent à la fois aux définitions de type de données cible et à la définition de format des fichiers. Si des rangées des fichiers sources ne correspondent pas aux options de format que vous avez spécifiées, l'interrogation signale une erreur. Vous pouvez utiliser les options du paramètre format, par exemple rejectlimit, pour supprimer ces erreurs. Vous pouvez également valider la table externe que vous avez créée pour voir les messages d'erreur et les rangées rejetées afin de pouvoir modifier les options de format en conséquence. Voir Valider les données externes pour plus d'informations.

    Pour des informations détaillées sur les paramètres, voir Procédure CREATE_EXTERNAL_TABLE.

Valider des données externes

Pour valider une table externe, utilisez la procédure DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Avant de valider une table externe, vous devez créer celle-ci à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE. Utilisez ensuite la procédure DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE pour valider la table. Par exemple : 

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS_EXT' );
END;
/

Cette procédure balaye vos fichiers sources et les valide à l'aide des options de format indiquées lors de la création de la table externe.

Par défaut, l'opération de validation balaye toutes les lignes des fichiers sources et s'arrête lorsqu'une ligne est rejetée. Pour valider uniquement un sous-ensemble de rangées, utilisez le paramètre rowcount. Lorsque le paramètre rowcount est défini, l'opération de validation balaye les rangées et s'arrête, soit lorsqu'une rangée est rejetée, soit lorsque le nombre spécifié de rangées est validé sans erreur.

Par exemple, l'opération de validation suivante analyse 100 rangées et s'arrête lorsqu'une rangée est rejetée ou lorsque 100 rangées sont validées sans erreur :

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100 ); 
END; 
/

Si vous ne voulez pas que la validation s'arrête lorsqu'une rangée est rejetée et que vous voulez voir toutes les rangées rejetées, réglez le paramètre stop_on_error à FALSE. Dans ce cas, VALIDATE_EXTERNAL_TABLE balaye toutes les rangées et signale toutes les rangées rejetées.

Pour valider uniquement un sous-ensemble de rangées, utilisez le paramètre rowcount. Lorsque rowcount est défini et que stop_on_error est réglé à FALSE, l'opération de validation balaye les rangées et s'arrête, soit lorsque le nombre spécifié de rangées est rejeté, soit lorsque le nombre spécifié de rangées est validé sans erreur. Par exemple, l'exemple suivant balaye 100 rangées et s'arrête lorsque 100 rangées sont rejetées ou lorsque 100 rangées sont validées sans erreur : 

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100, 
     stop_on_error => FALSE );
END; 
/

Voir Procédure VALIDATE_EXTERNAL_TABLE pour des informations détaillées sur les paramètres de DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Reportez-vous à Voir les journaux pour la validation de données pour consulter les résultats des opérations de validation dans les tables dba_load_operations et user_load_operations.

Voir les journaux pour la validation de données

Après avoir validé une table externe, vous pouvez voir le résultat de l'opération de validation en interrogeant une table d'opérations de chargement :

  • dba_load_operations : Affiche toutes les opérations de validation.

  • user_load_operations : Affiche les opérations de validation dans votre schéma.

Vous pouvez utiliser ces tables pour consulter les données de validation de chargement. Par exemple, utilisez l'énoncé SELECT suivant pour interroger user_load_operations : 

SELECT table_name,owner_name,type,status,start_time,update_time,logfile_table,badfile_table
FROM user_load_operations
WHERE type = 'VALIDATE';


TABLE_NAME    OWNER_NAME  TYPE       STATUS     START_TIME     UPDATE_TIME    LOGFILE_TABLE    BADFILE_TABLE
------------- ----------  ---------- ---------- -------------- -------------  ---------------  ---------------
CHANNELS_EXT  SH          VALIDATE   COMPLETED  13-NOV-17...   13-NOV-17...   VALIDATE$21_LOG  VALIDATE$21_BAD

L'utilisation de cet énoncé SQL avec la clause WHERE dans la colonne TYPE affiche toutes les opérations de chargement de type VALIDATE.

La colonne LOGFILE_TABLE affiche le nom de la table que vous pouvez interroger pour consulter le journal d'une opération de validation. Par exemple, l'interrogation suivante affiche le journal de cette opération de validation : 

SELECT * FROM VALIDATE$21_LOG;

La colonne BADFILE_TABLE affiche le nom de la table que vous pouvez interroger pour voir les rangées où des erreurs se sont produites lors de la validation. Par exemple, l'interrogation suivante affiche les enregistrements rejetés pour l'opération de validation ci-dessus : 

SELECT * FROM VALIDATE$21_BAD;

Selon les erreurs présentes dans le journal et les rangées affichées dans BADFILE_TABLE, vous pouvez corriger l'erreur en supprimant la table externe à l'aide de la commande DROP TABLE et en la recréant en spécifiant les options de format correctes dans DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Note :

Les tables LOGFILE_TABLE et BADFILE_TABLE sont stockées pendant deux jours pour chaque opération de validation, puis supprimées automatiquement.