ORC、ParquetまたはAvroソース・ファイルを使用した外部データの問合せ

Autonomous Databaseでは、外部表を使用して、オブジェクト・ストアに格納されているORC、ParquetまたはAvroデータに簡単にアクセスできます。ORC、ParquetおよびAvroソースにはメタデータが組み込まれており、DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャはこのメタデータを利用して外部表の作成を簡素化できます。

データの構造を把握する必要はありません。DBMS_CLOUDがファイルを調べ、ORC、ParquetまたはAvroコンテンツを同等のOracle列およびデータ型に変換できます。必要なのは、オブジェクト・ストア内のデータの場所を把握し、そのタイプ(ORC、ParquetまたはAvro)を指定し、オブジェクト・ストア上のソース・ファイルにアクセスするための資格証明を持っていることのみです。

ノート

ORC、ParquetおよびAvroでは、外部表を使用するステップが非常に類似しています。次のステップは、Parquetフォーマットのソース・ファイルの作業を示しています。

この例では、ソース・ファイルsales_extended.parquetに、Parquetフォーマットのデータが含まれます。Autonomous Databaseで、このファイルを問合せするには、次のようにします:

  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の外部表を作成します。

    プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEは、次のものを含む、サポートされているクラウド・オブジェクト・ストレージ・サービスの外部ファイルをサポートします: Oracle Cloud Infrastructure Object Storage、Azure Blob 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"}'
     );
    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を指定します。

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

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

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

    • 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(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)
    
    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を使用して表にロードできます。

詳細は、Avro、ORCまたはParquetファイル用のCREATE_EXTERNAL_TABLEプロシージャおよびAvro、ORCまたはParquetファイル用のCOPY_DATAプロシージャを参照してください。

サポートされるクラウド・オブジェクト・ストレージ・サービスの詳細は、DBMS_CLOUD URI形式を参照してください。