Interrogation des données partitionnées externes avec l'organisation de fichier source au format de dossier

Utilisez DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE pour créer une table partitionnée externe et générer les informations de partitionnement à partir du chemin de fichier de stockage d'objet cloud.

Lorsque vous créez une table externe avec des fichiers de données au format de dossier, vous pouvez indiquer les types des colonnes de partition de deux manières :

  • Le paramètre column_list permet d'indiquer manuellement les colonnes et leur type de données. Reportez-vous à Interrogation des données partitionnées externes avec l'organisation de fichier source au format Hive pour obtenir un exemple d'utilisation du paramètre column_list.

  • Vous pouvez laisser DBMS_CLOUD dériver les colonnes de fichier de données et leur type à partir des informations des fichiers de données structurés tels que les fichiers de données Avro, ORC et Parquet. Dans ce cas, utilisez l'option partition_columns avec le paramètre format afin d'indiquer les noms de colonne et leur type de données pour les colonnes de partition. Vous n'avez pas besoin de fournir les paramètres column_list ou field_list.

Examinez les exemples de fichier source suivants dans la banque d'objets :

.../sales/USA/2020/01/sales1.parquet

.../sales/USA/2020/02/sales2.parquet

Pour créer une table externe partitionnée avec le chemin de fichier de stockage d'objet cloud définissant les partitions à partir des fichiers à l'aide de cet exemple de format de dossier, procédez comme suit :

  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 partitionnée externe sur vos fichiers source à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

    La procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE prend en charge les fichiers partitionnés 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 tous se trouver dans la même banque d'objets cloud.

    Exemple :

    BEGIN DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
        table_name => 'MYSALES',
        credential_name => 'DEF_CRED_NAME',
        file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales/*.parquet',
        format =>
            json_object('type' value 'parquet', 'schema' value 'first',
                        'partition_columns' value
                              json_array(
                                    json_object('name' value 'country', 'type' value 'varchar2(100)'),
                                    json_object('name' value 'year', 'type' value 'number'),
                                    json_object('name' value 'month', 'type' value 'varchar2(2)')
                              )
             )
        );
    END;
    /

    Les paramètres DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE pour les fichiers de données structurés, par exemple pour un fichier de données Parquet, ne nécessitent pas les paramètres column_list ou field_list. Les noms de colonne et les types de données sont dérivés pour les colonnes du premier fichier Parquet que la procédure analyse (ainsi, tous les fichiers doivent avoir la même forme). La liste des colonnes générées inclut les colonnes dérivées du nom d'objet. Ces colonnes ont les types de données indiqués avec le paramètre partition_columns de format.

    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 URI de fichier source séparés par des virgules. Il existe deux options pour cette liste :

      • Indiquez une liste d'URI de fichier individuels sans caractère générique séparés par des virgules.

      • Indiquez un URI de fichier unique avec des caractères génériques, où ces derniers ne peuvent se trouver qu'après la dernière barre oblique "/". Le caractère "*" peut être utilisé comme caractère générique pour représenter plusieurs caractères. Le caractère "?" peut être utilisé comme caractère générique pour représenter un seul caractère.

    • column_list : liste des noms de colonne et des types de données de la table externe, séparés par des virgules. La liste comprend les colonnes contenues dans le fichier ainsi que celles dérivées du nom d'objet.

      Le paramètre column_list n'est pas requis lorsque les fichiers de données sont des fichiers structurés (Parquet, Avro ou ORC).

    • field_list : identifie les champs dans les fichiers source et leurs types de données. La valeur par défaut est NULL, ce qui signifie que les champs et leurs types de données sont déterminés par le paramètre column_list.

      Le paramètre field_list n'est pas requis lorsque les fichiers de données sont des fichiers structurés (Parquet, Avro ou ORC).

    • format : définit les options que vous pouvez indiquer pour décrire le format du fichier source. Le paramètre partition_columns de format indique les noms des colonnes de partition. Pour plus d'informations, reportez-vous à DBMS_CLOUD Options de format de package.

      Si les données de votre fichier source sont cryptées, déchiffrez-les en indiquant l'option de format encryption. Pour plus d'informations sur le décryptage des données, reportez-vous à Décryptage des données lors de l'import à partir d'Object Storage.

    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.

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

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

    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 partitionnée 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 à Modification des données externes et à Modification des données partitionnées externes.

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

    Votre instance Autonomous Database tire parti des informations de partitionnement de la table partitionnée externe, ce qui garantit que la requête accède uniquement aux fichiers de données pertinents dans la banque d'objets. Par exemple, la requête suivante lit uniquement les fichiers de données d'une partition.

    Exemple :

    SELECT year, month, product, units 
    FROM SALES WHERE year='2020' AND month='02' AND country='USA'

    Les tables partitionnées externes que vous créez avec DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE incluent deux colonnes invisibles : file$path et file$name. Ces colonnes permettent d'identifier le fichier d'où provient un enregistrement. Pour plus d'informations, reportez-vous à Colonnes de métadonnées de table externe.