Interrogation des données externes avec des fichiers source ORC, Parquet ou Avro

Lorsque vous accédez aux données ORC, Parquet ou Avro stockées dans une banque d'objets à l'aide de tables externes sur Autonomous Database, vous pouvez définir automatiquement ou explicitement la taille maximale des colonnes de texte.

1. Stockez les informations d'identification de la banque d'objets pour accéder à la banque d'objets, à l'aide de la procédure DBMS_CLOUD.CREATE_CREDENTIAL :

   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 pour ORC, Parquet ou Avro sur vos fichiers source à l'aide de la procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE et indiquez le paramètre de format maxvarchar.

   La procédure DBMS_CLOUD.CREATE_EXTERNAL_TABLE prend en charge les fichiers externes dans les services de stockage d'objets cloud pris en charge, notamment Oracle Cloud Infrastructure Object Storage, Azure Blob Storage ou Azure Data Lake Storage, Amazon S3 et Amazon S3-Compatible, notamment Oracle Cloud Infrastructure Object Storage, Google Cloud Storage et Wasabi Hot Cloud Storage. 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 défaut, les colonnes créées dans la table externe mettent automatiquement en correspondance leurs types de données avec les types de données Oracle pour les champs trouvés dans les fichiers source et les noms de colonne de table externe correspondent aux noms de champ source.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"extended"}'
    );
   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. Le paramètre credential_name doit être conforme aux conventions de dénomination des objets Oracle. Pour plus d'informations, reportez-vous à Règles de dénomination des objets de base de données.

   • file_uri_list : liste des fichiers source à interroger séparés par une virgule. Le format d'URI pour les adresses dédiées est pris en charge dans les domaines commerciaux (OC1). Pour plus d'informations, reportez-vous à Adresses dédiées Object Storage et à Formats d'URI DBMS_CLOUD.

   • format : définit les options que vous définissez pour décrire le format du fichier source. Pour un fichier Parquet, utilisez le paramètre format pour indiquer type parquet. Pour un fichier Avro, utilisez le paramètre format pour indiquer le fichier type avro. Pour un fichier ORC, utilisez le paramètre format pour indiquer type orc.

     L'option maxvarchar avec la valeur extended indique que les colonnes de texte sont créées en tant que varchar(32767) sur une instance Autonomous Database avec une taille de chaîne étendue. Les valeurs possibles sont standard avec varchar(4000), extended avec varchar(32767) et auto. La valeur maxvarchar par défaut est auto. Avec cette valeur, la taille maximale du texte est basée sur la valeur de MAX_STRING_SIZE.

   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.

   Par défaut, le paramètre format schema est défini, les colonnes et les types de données sont dérivés automatiquement et les champs de la source correspondent par nom aux colonnes de la table externe. Les types de données source sont convertis en types de données Oracle de colonne de table externe en fonction du mapping DBMS_CLOUD pour les types de données ORC, Parquet ou Avro. Les valeurs de paramètre schema valides sont les suivantes :

   • first : analysez le schéma du premier fichier ORC, Parquet ou Avro trouvé par DBMS_CLOUD dans le fichier file_uri_list indiqué (first est la valeur par défaut pour schema).

   • all : analysez tous les schémas pour tous les fichiers ORC, Parquet ou Avro trouvés dans file_uri_list. Comme il s'agit simplement de fichiers capturés dans une banque d'objets, il n'y a aucune garantie que les métadonnées de chaque fichier sont identiques. Par exemple, File1 peut contenir un champ appelé "adresse", tandis que ce champ peut être manquant dans File2. L'examen de chaque fichier pour dériver les colonnes est un peu plus coûteux, mais peut être nécessaire si le premier fichier ne contient pas tous les champs requis.

   Remarque
   
   Si le paramètre column_list est indiqué, vous indiquez les noms de colonne et les types de données de la table externe et la valeur schema, si elle est indiquée, est ignorée. Avec column_list, vous pouvez limiter les colonnes de la table externe. Si column_list n'est pas indiqué, la valeur par défaut schema est first.
3. Vous pouvez maintenant exécuter des requêtes sur la table externe que vous avez créée à l'étape précédente:

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(32767) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(32767) 
   CITY                 VARCHAR2(32767) 
   STATE_PROVINCE       VARCHAR2(32767) 
   INCOME_LEVEL         VARCHAR2(32767)
   

   SELECT prod_id, quantity_sold, gender, city, income_level 
        FROM sales_extended_ext 
        WHERE ROWNUM < 8;
   
        PROD_ID     QUANTITY_SOLD     GENDER    CITY              INCOME_LEVEL
   1    13          1                 M         Adelaide          K: 250,000 – 299,999
   2    13          1                 M         Dolores           L: 300,000 and above
   3    13          1                 M         Cayuga            F: 110,000 – 129,999
   4    13          1                 F         Bergen op Zoom    C: 50,000 – 69,999
   5    13          1                 F         Neuss             J: 190,000 – 249,999
   6    13          1                 F         Darwin            F: 110,000 – 129,999
   7    13          1                 M         Sabadell          K:250,000 – 299,999
   

   Cette requête affiche les valeurs des lignes de la table externe. Si vous souhaitez interroger fréquemment ces données, après avoir examiné les données, vous pouvez les charger dans une table avec DBMS_CLOUD.COPY_DATA.

   Si vous indiquez l'option format maxvarchar comme standard, les colonnes de texte varchar2() sont créées avec la taille 4000. Par exemple :

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"standard"}'
    );
   END;
   /     
   

   Le paramètre format avec l'option maxvarchar définie sur la valeur standard indique que les colonnes de texte sont créées en tant que varchar(4000).

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(4000) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(4000) 
   CITY                 VARCHAR2(4000) 
   STATE_PROVINCE       VARCHAR2(4000) 
   INCOME_LEVEL         VARCHAR2(4000)