Interroger des tables externes avec partitionnement implicite

Sur Autonomous AI Database, utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE pour créer des tables externes partitionnées implicites à partir de données partitionnées de style Hive ou de données partitionnées de dossier simples stockées dans votre banque d'objets cloud.

En transmettant les options appropriées à cette procédure, les partitions sont dérivées des données source. Les tables externes partitionnées prennent en charge le repérage d'exécution des colonnes de partition et de leurs valeurs. Le repérage d'exécution des modifications dans la structure de banque d'objets sous-jacente, telles que l'ajout ou la suppression d'objets, simplifie le processus de maintenance en éliminant la nécessité de procédures de synchronisation supplémentaires requises par DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE. Cela garantit que les données sont à jour lors de l'exécution de la requête.

Avec le partitionnement implicite, Autonomous AI Database détermine automatiquement les colonnes sur lesquelles une table est "partitionnée" en fonction de la structure de fichier hiérarchique de la source de stockage d'objets. Il n'est pas nécessaire de déclarer explicitement un schéma de partitionnement. Le partitionnement implicite offre des avantages en matière de performances de type table de partition sans avoir à définir explicitement une table externe partitionnée à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

Afin d'optimiser le temps de réponse aux requêtes pour les requêtes de très grands ensembles de données à l'aide du partitionnement implicite, vous pouvez activer l'optimisation en définissant strict_column_order sur True. Le paramètre strict_column_order est une sous-option de l'option de format implicit_partition_config. Cette optimisation s'applique uniquement lorsque partition_type est une ruche. Pour obtenir un exemple, reportez-vous à Requête de données partitionnées implicites externes avec l'organisation de fichier source de format Hive et à Options de format de package DBMS_CLOUD pour plus d'informations sur les paramètres de format de partitionnement implicite.

Les tables externes partitionnées implicites prennent en charge les styles de dénomination suivants pour les objets partitionnés dans la banque d'objets :

• Format de dénomination Hive : les données dans le stockage d'objets qui ont été générées à partir de Hive ont le format suivant.

  OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet

  Pour obtenir un exemple, reportez-vous à Interrogation de données partitionnées implicites externes avec l'organisation de fichier source au format Hive.

• Format de dénomination "Pathtail" non Hive : un deuxième format souvent trouvé dans les lacs de données est similaire au format de ruche, mais le nom du dossier n'inclut pas les colonnes de partition.

  OBJBUCKET/<table>/<value1>/<value2>/file.parquet

  Pour obtenir un exemple, reportez-vous à Requête de données de style non-Hive partitionnées implicites externes.

Pour plus d'informations sur les formats de dénomination, reportez-vous à A propos des tables externes avec partitionnement de fichier source.

Pour plus d'informations, reportez-vous à Requête de données partitionnées implicites externes avec l'organisation de fichier source au format Hive et à Procédure CREATE_EXTERNAL_TABLE.

Utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE pour créer une table externe partitionnée implicite à partir de données de la banque d'objets générées à partir de données Hive.

1. Stockez les 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 => 'MY_CREDENTIAL',
       username => 'adb_user@example.com',
       password => 'password' );
   END;
   /

   La création d'informations d'identification pour accéder à la banque d'objets Oracle Cloud Infrastructure 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 à des 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 pour le nom des informations d'identification. Cette étape n'est requise qu'une seule fois, sauf si vos 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 créer des tables externes.

   Pour plus d'informations sur les paramètres username et password pour différents services Object Storage, reportez-vous à Procédure CREATE_CREDENTIAL.

2. Créez une table externe partitionnée implicite sur vos fichiers source à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
   1. Cas 1 : activer le partitionnement implicite.
      Dans cet exemple, l'option de format implicit_partition_columns n'étant pas fournie, les colonnes de partition sont automatiquement détectées et le partitionnement implicite est activé en définissant l'option de format implicit_partition_type sur hive.

      Toutefois, si une liste de colonnes est fournie, ces colonnes sont utilisées en tant que colonnes de partition et Autonomous AI Database n'essaie pas de repérer les colonnes.

      Pour détecter les colonnes de partition, la base de données Autonomous AI commence à rechercher '=' au début du chemin, indiqué par file_uri_list. Lorsqu'elle est trouvée, la partie gauche de '=' jusqu'au dernier '/' est prise comme colonne (par exemple, "country") et la partie droite de '=', jusqu'au premier '/' comme valeur (par exemple, "USA"). La recherche continue pour '=' jusqu'après le '/' qui suit la première valeur de partition, en détectant le second '=' dans le chemin, etc.

      BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
         table_name        => 'mysales',
         credential_name   => 'MY_CREDENTIAL',
         file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
         column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
         format            => '{"type":"parquet", "implicit_partition_type":"hive"');
      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 URI du fichier source, séparés par des virgules. Deux options sont disponibles pour cette liste :

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

        • Spécifiez un URI de fichier unique avec des caractères génériques, où les caractères génériques ne peuvent être 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.

        Dans cet exemple, namespace-string est l'espace de noms 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.

      • column_list est une liste délimitée par des virgule de noms de colonne et de type de données pour une table externe. La liste inclut les colonnes du fichier de données et celles dérivées du nom de l'objet (à partir des noms figurant dans le chemin de fichier indiqué par file_uri_list).

        column_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. L'option implicit_partition_type indique le type de format de données Hive.

        Si les données du fichier source sont cryptées, décryptez-les en spécifiant 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.

        Pour plus d'informations, reportez-vous à DBMS_CLOUD Options de format de package.

   1. Cas 2 : Partitionnement implicite optimisé
      Dans cet exemple, le partitionnement implicite est activé en spécifiant l'option de format implicit_partition_config et le partitionnement implicite optimisé est activé en définissant strict_column_order sur True.

      Etant donné que partition_columns indique une liste de colonnes partitionnées, ces colonnes sont utilisées en tant que colonnes de partition et Autonomous AI Database n'essaie pas de repérer les colonnes.

      BEGIN
      dbms_cloud.create_external_table (
         table_name        => 'mysales',
         credential_name   => 'MY_CREDENTIAL',
         file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
         column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
         format            => '{"type":"parquet",
                                "implicit_partition_config":{
                                          "partition_type":"hive",
                                          "strict_column_order":true,
                                          "partition_columns":["country","year","month","day"]
                                }
                                }');
      END;
      /

      Les paramètres de format sont les suivants :

      • format : définit les options que vous pouvez indiquer pour décrire le format du fichier source. L'option implicit_partition_config comporte les sous-options suivantes :

        • partition_type

          La seule valeur acceptée est "hive". La valeur par défaut est le chemin du nom de fichier non Beehive. Si partition_type n'est pas spécifié, une erreur client est renvoyée.

        • partition_columns

          Liste des colonnes partitionnées.

        • strict_column_order

          Définissez la valeur sur true pour activer le partitionnement implicite optimisé. Définissez la valeur sur true uniquement pour les cas où vous savez que le chemin de la banque d'objets ne changera pas et qu'il n'y a aucun préfixe manquant. Définissez la valeur sur false ou omettez pour la désactiver.

        Si les données du fichier source sont cryptées, décryptez-les en spécifiant 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.

        Pour plus d'informations, reportez-vous à DBMS_CLOUD Options de format de package.

   Pour plus d'informations sur les paramètres, reportez-vous à Procédure CREATE_EXTERNAL_TABLE. Pour plus d'informations sur les services de stockage d'objets cloud pris en charge, reportez-vous à Formats d'URI DBMS_CLOUD.

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 base de données Autonomous AI tire parti des informations de partitionnement de votre table partitionnée externe, ce qui garantit que la requête n'accède qu'aux fichiers de données pertinents dans la banque d'objets.

   Par exemple :

   SELECT product, units FROM mysales WHERE year='2024' and month='02'

   Cette instruction SQL interroge uniquement les données de la partition pour le mois 02 de l'année 2024.

• Optimiser la planification des requêtes de banque d'objets avec l'option strict_column_order
  Lorsqu'une requête cible un dossier de banque d'objets avec un très grand nombre de fichiers et de sous-dossiers, la phase de planification et de création de listes peut devenir le coût principal avant que les données ne soient réellement analysées. Ceci est courant avec les mises en page de dossiers de type Hive où les valeurs de partition sont intégrées dans le chemin.

Utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE pour créer une table externe partitionnée implicite à partir des données du stockage d'objets qui ont été générées à partir de données non Hive.

1. Stockez les 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 => 'MY_CREDENTIAL',
       username => 'adb_user@example.com',
       password => 'password' );
   END;
   /

   La création d'informations d'identification pour accéder à la banque d'objets Oracle Cloud Infrastructure 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 à des 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 pour le nom des informations d'identification. Cette étape n'est requise qu'une seule fois, sauf si vos 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 créer des tables externes.

   Pour plus d'informations sur les paramètres username et password pour différents services Object Storage, reportez-vous à Procédure CREATE_CREDENTIAL.

2. Créez une table externe partitionnée implicite sur vos fichiers source à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
   Dans cet exemple, le partitionnement implicite est activé en fournissant aux colonnes de partition l'option de format implicit_partition_columns. Etant donné que le nom du dossier n'inclut pas les colonnes de partition, les valeurs de partition du chemin, indiquées par file_uri_list, ne peuvent être repérées que si une liste explicite de colonnes est fournie avec l'option de format implicit_partition_columns. Pour détecter les valeurs de colonne respectives dans le chemin, l'ordre des colonnes doit être identique à l'ordre des valeurs.

   Les colonnes de partition sont automatiquement détectées.

   BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
      table_name        => 'mysales',
      credential_name   => 'MY_CREDENTIAL',
      file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
      column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
      format            => '{"type":"parquet", "implicit_partition_columns":["country","year","month"]}');
   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 URI du fichier source, séparés par des virgules. Deux options sont disponibles pour cette liste :

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

     • Spécifiez un URI de fichier unique avec des caractères génériques, où les caractères génériques ne peuvent être 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 est une liste délimitée par des virgule de noms de colonne et de type de données pour une table externe. La liste inclut les colonnes du fichier de données et celles dérivées du nom de l'objet (à partir des noms figurant dans le chemin de fichier indiqué par file_uri_list).

     column_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. L'option implicit_partition_type n'est pas définie. Etant donné que implicit_partition_columns est indiqué, le type est automatiquement détecté comme non Beehive.

     Si les données du fichier source sont cryptées, décryptez-les en spécifiant 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.

     Pour plus d'informations, reportez-vous à DBMS_CLOUD Options de format de package.

   Dans cet exemple, namespace-string est l'espace de noms 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 plus d'informations sur les paramètres, reportez-vous à Procédure CREATE_EXTERNAL_TABLE.

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

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 base de données Autonomous AI tire parti des informations de partitionnement de votre table partitionnée externe, ce qui garantit que la requête n'accède qu'aux fichiers de données pertinents dans la banque d'objets.

   Par exemple :

   SELECT product, units FROM mysales WHERE year='2024'

   Cette instruction SQL interroge uniquement les données de la partition pour l'année 2024.