Autonomous Databaseの外部表を使用してオブジェクト・ストアに格納されているORC、ParquetまたはAvroデータにアクセスする場合、テキスト列の最大サイズを自動または明示的に設定できます。

1. プロシージャDBMS_CLOUD.CREATE_CREDENTIALを使用して、オブジェクト・ストアにアクセスするためのオブジェクト・ストア資格証明を格納します:

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

   リソース・プリンシパル資格証明を有効にする場合、Oracle Cloud Infrastructure Object Storeにアクセスするための資格証明を作成する必要はありません。詳細は、リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセスを参照してください。

   この操作では、暗号化された形式で資格証明がデータベースに格納されます。資格証明名には任意の名前を使用できます。このステップは、オブジェクト・ストアの資格証明を変更しないかぎり1回のみ必要です。資格証明を保存したら、外部表の作成に同じ資格証明名を使用できます。

   様々なオブジェクト・ストレージ・サービスのusernameおよびpasswordパラメータの詳細は、CREATE_CREDENTIALプロシージャを参照してください。

2. プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用して、ORC、ParquetまたはAvroの外部表をソース・ファイルに作成し、maxvarcharフォーマット・パラメータを指定します。

   プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEは、次のものを含む、サポートされているクラウド・オブジェクト・ストレージ・サービスの外部ファイルをサポートします: Oracle Cloud Infrastructure Object Storage、Azure Blob StorageまたはAzure Data Lake Storage、Amazon S3、Amazon S3互換(Oracle Cloud Infrastructure Object Storage、Google Cloud Storage、Wasabi Hot Cloud Storageを含む)。資格証明は表レベルのプロパティであるため、外部ファイルは同じオブジェクト・ストアに存在する必要があります。

   デフォルトでは、外部表で作成された列のデータ型は、ソース・ファイルにあるフィールドのOracleデータ型に自動的にマップされ、外部表の列名はソース・フィールド名と一致します。

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

   パラメータは次のとおりです:

   • table_name: 外部表の名前です。

   • credential_name: 前のステップで作成された資格証明の名前です。credential_nameパラメータは、Oracleオブジェクトのネーミング規則に準拠している必要があります。詳細は、「データベース・オブジェクトのネーミング規則」を参照してください。

   • file_uri_list: 問い合せるソース・ファイルのカンマ区切りリストです。専用エンドポイントのURI形式は、商用(OC1)レルムでサポートされています。詳細は、オブジェクト・ストレージの専用エンドポイントおよびDBMS_CLOUDのURIフォーマットを参照してください。

   • format: ソース・ファイルのフォーマットを記述するためのオプションを定義します。Parquetファイルの場合は、formatパラメータを使用してtype parquetを指定します。Avroファイルの場合は、formatパラメータを使用してtype avroを指定します。ORCファイルの場合は、formatパラメータを使用してtype orcを指定します。

     値がextendedのオプションmaxvarcharは、拡張文字列サイズを持つAutonomous Databaseインスタンスでテキスト列をvarchar(32767)として作成することを指定します。指定可能な値は、varchar(4000)が指定されたstandard、varchar(32767)が指定されたextendedおよびautoです。デフォルトのmaxvarchar値はautoです。この値の場合、最大テキスト・サイズはMAX_STRING_SIZEの値に基づきます。

   この例では、namespace-stringはOracle Cloud Infrastructureオブジェクト・ストレージ・ネームスペースで、bucketnameはバケット名です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。

   デフォルトでは、format schemaパラメータが設定され、列およびデータ型が自動的に導出されて、ソースのフィールドが外部表の列と名前で一致します。ソース・データ型は、ORC、ParquetまたはAvroデータ型のDBMS_CLOUDマッピングに従って、外部表の列のOracleデータ型に変換されます。有効なschemaパラメータ値は次のとおりです:

   • first: DBMS_CLOUDが指定されたfile_uri_listで検出した最初のORC、ParquetまたはAvroファイルのスキーマを分析します(firstがschemaのデフォルト値です。

   • all: file_uri_listにあるすべてのORC、ParquetまたはAvroファイルのすべてのスキーマを分析します。これらは単にオブジェクト・ストアに取得されるファイルであるため、各ファイルのメタデータが同じである保証はありません。たとえば、File1には"address"というフィールドが含まれる場合がありますが、File2にはそのフィールドがない場合があります。各ファイルを調査して列を導出する方が多少コストがかかりますが、最初のファイルにすべての必須フィールドが含まれていない場合は必要になることがあります。

   ノート
   
   column_listパラメータを指定する場合は、外部表の列名とデータ型を指定し、schemaの値(指定した場合)は無視されます。column_listを使用して、外部表の列を制限できます。column_listが指定されていない場合、schemaのデフォルト値はfirstです。
3. これで、前述のステップで作成した外部表に対して問合せを実行できるようになります。

   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
   

   この問合せでは、外部表の行の値が表示されます。このデータを頻繁に問い合せる場合は、データを調査した後、DBMS_CLOUD.COPY_DATAを使用して表にロードできます。

   formatオプションmaxvarcharをstandardとして指定した場合、varchar2()テキスト列はサイズ4000で作成されます。たとえば:

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

   オプションmaxvarcharを値standardに設定したformatパラメータは、テキスト列を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)