Documentation Oracle Cloud Infrastructure

Interroger des tables externes avec partitionnement implicite

Sur Autonomous Database, vous pouvez 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.

Rubrique parent : Interrogation des données externes avec Autonomous Database

A propos des tables externes avec partitionnement implicite

Sur Autonomous 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.

Utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE pour créer des tables externes partitionnées implicites. 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 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.

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 :

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

A 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 manières suivantes :
  • Définir le type de partition sur Hive avec une liste donnée de colonnes de partition

    Dans ce cas, implicit_partition_type est défini sur hive et implicit_partition_columns fournit la liste des colonnes de partition.

    Par exemple :

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"]}');
  • Définir le type de partition sur Hive sans fournir de liste de colonnes de partition

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

    Par exemple :

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"');
  • Fournir une liste de colonnes de partition sans indiquer le type

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

    Par exemple :

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"]}');

Pour plus d'informations, reportez-vous à Procédure CREATE_EXTERNAL_TABLE.

Interrogation de données partitionnées implicites externes avec l'organisation du fichier source du format Hive

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.

Les exemples de fichiers source dans l'exemple suivant utilisent le format de dénomination suivant :

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

Examinez les exemples de fichiers 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 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 à 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, 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 Database n'essaie pas de repérer les colonnes.

    Pour détecter les colonnes de partition, Autonomous Database commence à rechercher '=' à partir du 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. 

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'DEF_CRED_NAME',
   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"');

    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 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.

    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 instance Autonomous Database tire parti des informations de partitionnement de votre table partitionnée externe, en veillant à ce 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.

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.

Les exemples de fichiers source dans l'exemple suivant utilisent le format de dénomination suivant :

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

Examinez les exemples de fichiers 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 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 à 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. 
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'DEF_CRED_NAME',
   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"]}');

    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 instance Autonomous Database tire parti des informations de partitionnement de votre table partitionnée externe, en veillant à ce 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.