Interrogation de données externes avec Autonomous Database on Dedicated Exadata Infrastructure

Description des packages et des outils permettant d'interroger et de valider des données avec Autonomous Database on Dedicated Exadata Infrastructure.

Les données externes ne sont pas gérées par la base de données, mais vous pouvez utiliser les procédures DBMS_CLOUD pour interroger ces données externes. Bien que les requêtes sur les données externes ne soient pas aussi rapides que les requêtes sur les tables de base de données, vous pouvez adopter cette approche pour lancer rapidement l'exécution des requêtes sur les fichiers source externes et les données externes.

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

Remarques :

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

Interrogation des données externes

Pour interroger des données dans des fichiers dans le cloud, vous devez d'abord stocker vos informations d'identification de stockage d'objet 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 informations d'identification du stockage d'objet cloud à 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 fournies pour username et password dépendent du service Cloud Object Storage que vous utilisez :

    • Oracle Cloud Infrastructure Object Storage : username est votre nom utilisateur Oracle Cloud Infrastructure et password est votre jeton d'authentification Oracle Cloud Infrastructure. Reportez-vous à Utilisation des jetons d'authentification.

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

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

  2. Créez une table externe sur vos fichiers source à 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'objet cloud pris en charge. Les informations d'identification sont des propriétés de niveau table. Par conséquent, les fichiers externes doivent se trouver dans la même banque d'objets.

    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 informations d'identification créées à l'étape précédente.

    • file_uri_list : liste des fichiers source à interroger séparés par une virgule.

      Dans cet exemple, file_uri_list est un URI Swift Oracle Cloud Infrastructure qui indique le fichier channels.txt dans le bucket mybucket de la région us-phoenix-1. (idthydc0kinr est l'espace de noms du stockage d'objet dans lequel le bucket réside.) Pour plus d'informations sur les formats d'URI pris en charge, reportez-vous à Formats d'URI de stockage d'objet cloud.

    • format : définit les options que vous pouvez indiquer pour décrire le format du fichier source. Pour plus d'informations sur les options de format que vous pouvez fournir, reportez-vous à Paramètre de format.

    • column_list : liste des définitions de colonne dans les fichiers source, séparées par une virgule.

    Vous pouvez maintenant exécuter des requêtes sur la table externe que vous avez créée à l'étape précédente. Exemple :

    SELECT count(*) FROM channels_ext;

    Par défaut, la base de données s'attend à ce que toutes les lignes des fichiers de données externes soient valides, et à ce qu'elles correspondent à la fois aux définitions de type de données cible et à la définition de format des fichiers. Si des lignes des fichiers source ne correspondent pas aux options de format spécifiées, la requête signale une erreur. Vous pouvez utiliser les options de paramètre format telles que rejectlimit pour supprimer ces erreurs. Vous pouvez également valider la table externe que vous avez créée pour afficher les messages d'erreur et les lignes rejetées afin de pouvoir modifier les options de format en conséquence. Pour plus d'informations, reportez-vous à Validation des données externes.

    Pour obtenir des informations détaillées sur les paramètres, reportez-vous à Procédure CREATE_EXTERNAL_TABLE.

Validation 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 la créer à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE. Utilisez ensuite la procédure DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE pour la valider. Exemple : 

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

Cette procédure analyse vos fichiers source et les valide à l'aide des options de format spécifiées lors de la création de la table externe.

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

Par exemple, l'opération de validation suivante analyse 100 lignes et s'arrête lorsqu'une ligne est rejetée ou lorsque 100 lignes 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 ligne est rejetée et que vous voulez visualiser toutes les lignes rejetées, définissez le paramètre stop_on_error sur FALSE. Dans ce cas, VALIDATE_EXTERNAL_TABLE analyse toutes les lignes et signale toutes les lignes rejetées.

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

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

Pour obtenir des informations détaillées sur les paramètres DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE, reportez-vous à Procédure VALIDATE_EXTERNAL_TABLE.

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

Affichage des journaux pour la validation des données

Après avoir validé une table externe, vous pouvez consulter 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 visualiser les informations de validation de chargement. Par exemple, utilisez l'instruction SELECT suivante 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 cette instruction SQL avec la clause WHERE sur la colonne TYPE affiche toutes les opérations de chargement de type VALIDATE.

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

SELECT * FROM VALIDATE$21_LOG;

La colonne BADFILE_TABLE indique le nom de la table que vous pouvez interroger pour consulter les lignes qui ont rencontré des erreurs lors de la validation. Par exemple, la requête suivante affiche les enregistrements rejetés pour l'opération de validation ci-dessus : 

SELECT * FROM VALIDATE$21_BAD;

En fonction des erreurs affichées dans le journal et des lignes 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 avec les options de format correctes dans DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Remarques :

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