Documentation sur Oracle Cloud Infrastructure

Interroger des tables externes avec partitionnement implicite

Sur Autonomous AI Database, vous pouvez créer des tables externes partitionnées implicitement à partir de données partitionnées de style Hive ou à partir de données partitionnées à dossier simple stockées dans votre magasin d'objets en nuage.

Rubrique parent : Interroger des données externes avec une base de données d'intelligence artificielle autonome

À propos des tables externes avec partitionnement implicite

Dans 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 à partir de données partitionnées par dossier simple stockées dans votre magasin d'objets en nuage.

En transmettant les options appropriées à cette procédure, les partitions sont dérivées des données sources. Les tables externes partitionnées prennent en charge la détection d'exécution des colonnes de partition et de leurs valeurs. La détection à l'exécution des modifications apportées à la structure du magasin d'objets sous-jacent, telles que l'ajout ou la suppression d'objets, simplifie le processus de maintenance en éliminant le besoin de procédures de synchronisation supplémentaires requises par DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE. Cela garantit que les données sont à jour au moment de l'exécution de l'interrogation.

Avec le partitionnement implicite, Autonomous AI Database détermine automatiquement les colonnes sur lesquelles une table est "partitionnée" en fonction de la structure hiérarchique du fichier source du 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 qu'il soit nécessaire de 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 des interrogations pour les interrogations de jeux de données très volumineux à l'aide du partitionnement implicite, vous pouvez activer l'optimisation en réglant strict_column_order à Vrai. Le paramètre strict_column_order est une sous-option de l'option de format implicit_partition_config. Cette optimisation ne s'applique que lorsque partition_type est ruche. Voir Interroger les données partitionnées implicites externes avec l'organisation du fichier source au format Hive pour un exemple et Options de format d'ensemble 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 d'attribution de nom suivants pour les objets partitionnés dans le magasin d'objets :

Pour plus d'informations sur les formats d'attribution de nom, voir À propos des tables externes avec partitionnement de fichier source.

À l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE, les tables externes partitionnées implicites peuvent être créées de l'une des façons suivantes :
  • Optimiser le partitionnement implicite. Utilisez l'option implicit_partition_config pour activer le partitionnement implicite et l'optimisation du temps d'interrogation écoulé.

    Dans cet exemple, le partitionnement implicite est activé en réglant partition_type à ruche. La seule valeur valide pour partition_type est ruche. L'optimisation est activée en réglant setting strict_column_order à Vrai. L'option partition_columns spécifie les colonnes qui sont partitionnées.

    Exemple :

    BEGIN
dbms_cloud.create_external_table (
   table_name        => 'partitions1',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'c varchar2(100), y number, total_sales number',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["org","tenant","dbtype","year","month","day"]
                          }
                          }');
END;
/
  • Définir le type de partition pour rucher avec une liste de colonnes de partition donnée

    Dans ce cas, implicit_partition_type est réglé à hive et implicit_partition_columns fournit une liste de colonnes de partition.

    Exemple :

    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",
                          "implicit_partition_columns":["country","year","month"]}');
END;
/
  • Définir le type de partition à ruche sans fournir de liste de colonnes de partition

    Dans ce cas, implicit_partition_type est réglé à hive et implicit_partition_columns n'est pas fourni. Les colonnes de partition sont automatiquement détectées en recherchant '=' dans le chemin spécifié par file_uri_list. La valeur column_name est à gauche de '=' et la valeur est sur le côté droit. Si column_name est introuvable dans le chemin, une erreur est générée.

    Exemple :

    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;
/
  • Fournir une liste de colonnes de partition sans spécifier le type

    Dans ce cas, implicit_partition_type n'est pas défini et implicit_partition_columns fournit une liste de colonnes.

    Exemple :

    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;
/

Pour plus d'informations, voir Interroger les données partitionnées implicites externes au moyen de l'organisation du fichier source au format Hive et Procédure CREATE_EXTERNAL_TABLE.

Interroger des données partitionnées implicites externes avec organisation de fichiers sources au format Hive

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

Dans l'exemple suivant, utilisez le format d'attribution de nom suivant :

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

Examinez les exemples de fichier source suivants :

OBJBUCKET/sales/country=USA/year=2024/month=01/sales-2024-01.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-02.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-03.parquet

Pour créer une table externe partitionnée implicite avec des données stockées dans cet exemple de format Hive, procédez comme suit :

  1. Stockez les données d'identification du magasin d'objets à l'aide de la procédure DBMS_CLOUD.CREATE_CREDENTIAL.

    Exemple :

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

    La création de données d'identification pour accéder au magasin d'objets Oracle Cloud Infrastructure n'est pas requise si vous activez les données d'identification du principal de ressource. Pour plus d'informations, voir Utiliser un principal de ressource pour accéder aux ressources Oracle Cloud Infrastructure.

    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 le nom des 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 les données d'identification stockées, vous pouvez utiliser le même nom pour créer des tables externes.

    Voir Procédure CREATE_CREDENTIAL pour plus d'informations sur les paramètres username et password pour différents services de stockage d'objets.

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

      Toutefois, si une liste de colonnes est indiquée, ces colonnes sont utilisées en tant que colonnes de partition et Autonomous AI Database ne tente pas de détecter les colonnes.

      Pour détecter les colonnes de partition, la base de données autonome d'IA commence la recherche à partir du début du chemin, spécifié par file_uri_list, pour '='. Lorsqu'elle est trouvée, la partie gauche de '=' jusqu'à la dernière colonne '/' est prise comme colonne (par exemple "pays") et la partie droite de '=', jusqu'à la première valeur '/' (par exemple "USA"). La recherche continue pour '=' jusqu'à ce qu'après le '/' qui suit la première valeur de partition, détectant le deuxième '=' dans le chemin, et ainsi de suite. 

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

      • file_uri_list : Il s'agit d'une liste délimitée par des virgules d'URI de fichier source. Deux options sont disponibles pour cette liste :

        • Indiquez une liste délimitée par des virgules d'URI de fichier individuels 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 se trouver qu'après la dernière barre oblique "/". Le caractère "*" peut être utilisé comme caractère générique pour plusieurs caractères, "?" peut être utilisé pour un seul caractère.

        Dans cet exemple, namespace-string est l'espace de noms du stockage d'objets pour Oracle Cloud Infrastructure et bucketname est le nom du seau. Pour plus d'informations, voir Présentation des espaces de noms du stockage d'objets.

      • column_list : Liste séparée par des virgules de noms de colonne et de types de données pour la 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 du chemin d'accès spécifié par file_uri_list).

        La valeur column_list n'est pas requise lorsque les fichiers de données sont des fichiers structurés (Parquet, Avro ou ORC).

      • format : Définit les options que vous pouvez spécifier pour décrire le format du fichier source. L'option implicit_partition_type spécifie le type de format de données comme ruche.

        Si les données de votre fichier source sont chiffrées, déchiffrez les données en spécifiant l'option de format encryption. Voir Décrypter les données lors de l'importation à partir du stockage d'objets pour plus d'informations sur le déchiffrement des données.

        Pour plus d'informations, voir Options de format d'ensemble DBMS_CLOUD.

    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 réglant strict_column_order à Vrai.

      Comme partition_columns spécifie une liste de colonnes partitionnées, ces colonnes sont utilisées en tant que colonnes de partition et Autonomous AI Database ne tente pas de détecter 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 spécifier 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 un chemin de fichier sans nom de ruche. Si partition_type n'est pas spécifié, une erreur de client est retournée.

        • partition_columns

          Liste des colonnes partitionnées.

        • strict_column_order

          Réglez à true pour activer le partitionnement implicite optimisé. Réglez à true uniquement pour les cas où vous savez que le chemin du magasin d'objets ne changera pas et qu'il n'y a aucun préfixe manquant. Réglez à false ou omettez pour désactiver.

        Si les données de votre fichier source sont chiffrées, déchiffrez les données en spécifiant l'option de format encryption. Voir Décrypter les données lors de l'importation à partir du stockage d'objets pour plus d'informations sur le déchiffrement des données.

        Pour plus d'informations, voir Options de format d'ensemble DBMS_CLOUD.

    Voir Procédure CREATE_EXTERNAL_TABLE pour des informations détaillées sur les paramètres. Pour plus d'informations sur les services de stockage d'objets en nuage pris en charge, voir Formats d'URI DBMS_CLOUD.

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

    Votre base de données Autonomous AI Database tire parti des informations de partitionnement de votre table partitionnée externe, en s'assurant que l'interrogation accède uniquement aux fichiers de données pertinents du magasin d'objets.

    Exemple :

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

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

Rubrique parent : Interroger des tables externes avec partitionnement implicite

Optimiser la planification des interrogations du magasin d'objets avec l'option strict_column_order

Lorsqu'une interrogation cible un dossier de magasin d'objets avec un très grand nombre de fichiers et de sous-dossiers, la phase de planification et de liste peut devenir le coût principal avant que les données ne soient réellement balayées. Cela est courant avec les dispositions de dossier de style Hive où les valeurs de partition sont intégrées dans le chemin.

Note

  • Le paramètre strict_column_order est désactivé par défaut. Activer uniquement si la disposition du chemin est cohérente.

  • Si les conventions de dossier changent (par exemple, l'ajout, la suppression ou le réorganisation des colonnes de partition), vous devez mettre à jour l'option partition_columns et devrez peut-être désactiver cette option.

Activez l'option strict_column_order comme suit :

Si vos données utilisent des chemins partitionnés de type Hive et que les colonnes de partition apparaissent toujours dans un ordre fixe et cohérent sans segments manquants, activez l'optimisation en utilisant le paramètre suivant : 

strict_column_order = true  (with partition_type = "hive")

La base de données peut ignorer les répertoires inutiles en suivant l'ordre des partitions déjà définies. Cela signifie qu'il n'a pas besoin de répertorier chaque objet, ce qui peut accélérer considérablement le temps de planification pour les jeux de données volumineux.

Notes sur l'utilisation de l'option strict_column_order :

  • Les chemins suivent le nom et l'ordre Hive, par exemple :

    .../country=US/year=2025/month=09/...

  • Les colonnes de partition conservent un jeu et une séquence fixes, sans réordonnancement ni préfixes ignorés.

  • Vous devez optimiser la liste de planification pour les dossiers contenant un très grand nombre d'objets.
  • Vous ne pouvez pas utiliser cette option dans les jeux de données où tous les préfixes ne sont pas présents. Par exemple, certains dossiers incluent year=, tandis que d'autres commencent par month=).

Exemples : Utilisez l'option strict_column_order pour les partitions de type Hive avec DBMS_CLOUD.CREATE_EXTERNAL_TABLE

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name        => 'sales_xt',
    credential_name   => 'MY_CREDENTIAL',
    file_uri_list     => 'https://objectstorage.example.com/n/tenant/b/lake/o/sales/',
    -- Data is stored as: .../country=US/year=2025/month=09/*.parquet
    column_list       => 'product       VARCHAR2(100),
                          units         NUMBER,
                          amount        NUMBER,
                          country       VARCHAR2(30),
                          year          NUMBER,
                          month         NUMBER',
    format            => '{
      "type": "parquet",
      "implicit_partition_config": {
        "partition_type": "hive",
        "partition_columns": ["country","year","month"],
        "strict_column_order": true
      }
    }'
  );
END;
/

Vérifiez que la logique de planification évite de répertorier les chemins de niveau supérieur non associés (par exemple, les chemins appartenant à d'autres pays ou années) :

  • Exécutez une interrogation sélective qui restreint les partitions principales, par exemple :
    SELECT COUNT(*) FROM sales_xt WHERE country = ''US'' AND year = 2025;
    avec
    strict_column_order = true
    .
  • Si les hypothèses de disposition ne sont pas remplies, désactivez l'option et réessayez.

Interroger des données de style non Hive partitionnées implicites externes

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.

Dans l'exemple suivant, utilisez le format d'attribution de nom suivant :

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

Examinez les exemples de fichier source suivants :

OBJBUCKET/sales/USA/2024/01/sales-2024-01.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-02.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-03.parquet

Pour créer une table externe partitionnée implicite avec des données stockées dans cet exemple de format Hive, procédez comme suit :

  1. Stockez les données d'identification du magasin d'objets à l'aide de la procédure DBMS_CLOUD.CREATE_CREDENTIAL.

    Exemple :

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

    La création de données d'identification pour accéder au magasin d'objets Oracle Cloud Infrastructure n'est pas requise si vous activez les données d'identification du principal de ressource. Pour plus d'informations, voir Utiliser un principal de ressource pour accéder aux ressources Oracle Cloud Infrastructure.

    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 le nom des 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 les données d'identification stockées, vous pouvez utiliser le même nom pour créer des tables externes.

    Voir Procédure CREATE_CREDENTIAL pour plus d'informations sur les paramètres username et password pour différents services de stockage d'objets.

  2. Créez une table externe partitionnée implicite au-dessus de vos fichiers sources à 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. Comme le nom du dossier n'inclut pas les colonnes de partition, les valeurs de partition dans le chemin, spécifiées par file_uri_list, ne peuvent être détectées que si une liste explicite de colonnes est indiquée 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 le même que 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 données d'identification créées à l'étape précédente.

    • file_uri_list : Il s'agit d'une liste délimitée par des virgules d'URI de fichier source. Deux options sont disponibles pour cette liste :

      • Indiquez une liste délimitée par des virgules d'URI de fichier individuels 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 se trouver qu'après la dernière barre oblique "/". Le caractère "*" peut être utilisé comme caractère générique pour plusieurs caractères, "?" peut être utilisé pour un seul caractère.

    • column_list : Liste séparée par des virgules de noms de colonne et de types de données pour la 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 du chemin d'accès spécifié par file_uri_list).

      La valeur column_list n'est pas requise lorsque les fichiers de données sont des fichiers structurés (Parquet, Avro ou ORC).

    • format : Définit les options que vous pouvez spécifier pour décrire le format du fichier source. L'option implicit_partition_type n'est pas définie. Comme implicit_partition_columns est indiqué, le type est automatiquement détecté comme non ruche.

      Si les données de votre fichier source sont chiffrées, déchiffrez les données en spécifiant l'option de format encryption. Voir Décrypter les données lors de l'importation à partir du stockage d'objets pour plus d'informations sur le déchiffrement des données.

      Pour plus d'informations, voir Options de format d'ensemble DBMS_CLOUD.

    Dans cet exemple, namespace-string est l'espace de noms du stockage d'objets pour Oracle Cloud Infrastructure et bucketname est le nom du seau. Pour plus d'informations, voir Présentation des espaces de noms du stockage d'objets.

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

    Pour plus d'informations sur les services de stockage d'objets en nuage pris en charge, voir Formats d'URI DBMS_CLOUD.

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

    Votre base de données Autonomous AI Database tire parti des informations de partitionnement de votre table partitionnée externe, en s'assurant que l'interrogation accède uniquement aux fichiers de données pertinents du magasin d'objets.

    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.