Interrogation des données externes

Pour interroger les données dans des fichiers dans le cloud, vous devez d'abord stocker les informations d'identification de stockage d'objet dans votre instance Autonomous Database, puis créer une table externe à l'aide de la procédure PL/SQL DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Vous pouvez également utiliser la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE pour interroger des données externes dans les systèmes de fichiers joints ou dans le système de fichiers local.

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, notamment :

  • Oracle Cloud Infrastructure Object Storage

  • Azure Blob Storage ou Azure Data Lake Storage

  • Amazon S3

  • Compatibles avec Amazon S3, notamment : Oracle Cloud Infrastructure Object Storage, Google Cloud Storage et Wasabi Hot Cloud Storage.

  • Référentiel GitHub

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 de banque d'objets à l'aide de la procédure DBMS_CLOUD.CREATE_CREDENTIAL.

    Par exemple :

    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'adb_user@example.com',
        password => 'password' );
    END;
    /
    

    La création d'informations d'identification pour accéder à Oracle Cloud Infrastructure Object Storage n'est pas requise si vous activez les informations d'identification de principal de ressource. Pour plus d'informations, reportez-vous à Utilisation du principal de ressource pour accéder aux ressources Oracle Cloud Infrastructure.

    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 la création de tables externes.

    Reportez-vous à Procédure CREATE_CREDENTIAL pour plus d'informations sur les paramètres username et password des différents services de stockage d'objet.

  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.

    Par exemple :

    BEGIN
       DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
        table_name =>'CHANNELS_EXT',
        credential_name =>'DEF_CRED_NAME',
        file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt',
        format => json_object('delimiter' value ','),
        column_list => 'CHANNEL_ID VARCHAR2(2), CHANNEL_DESC VARCHAR2(20), CHANNEL_CLASS VARCHAR2(20)' );
    END;
    /
    

    Les paramètres sont les suivants :

    Dans cet exemple, namespace-string est l'espace de noms de stockage d'objet Oracle Cloud Infrastructure et bucketname est le nom du bucket. Pour plus d'informations, reportez-vous à Présentation des espaces de noms Object Storage.

    Remarque

    Autonomous Database prend en charge divers formats de fichier source, y compris les formats de données compressées. Reportez-vous à DBMS_CLOUD Options de format de package et à l'option de format de DBMS_CLOUD compression pour connaître les types de compression pris en charge.

    Vous pouvez maintenant exécuter des requêtes 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 lignes du fichier de données externe 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 paramètres DBMS_CLOUD tels 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.

    Pour plus d'informations sur les services de stockage d'objet cloud pris en charge, reportez-vous à Formats d'URI DBMS_CLOUD.

Colonnes de métadonnées de table externe

Les métadonnées de table externe vous aident à déterminer d'où proviennent les données lorsque vous exécutez une requête.

Les tables externes que vous créez avec DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE ou DBMS_CLOUD.CREATE_HYBRID_PART_TABLE incluent deux colonnes invisibles : file$path et file$name. Ces colonnes permettent d'identifier le fichier d'où provient un enregistrement.

  • file$path : indique le texte du chemin du fichier jusqu'au début du nom de l'objet.

  • file$name : indique le nom de l'objet, y compris tout le texte qui suit le signe "/" final.

Exemple :

SELECT genre_id, name, file$name, file$path FROM ext_genre
     WHERE rownum <= 2;

genre_id      name        file$name     file$path
--------      ---------   -----------   ----------------------
1             Action      genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
2             Adventure   genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre

Pour plus d'informations sur les colonnes non visibles, reportez-vous à Colonnes non visibles.