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

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

データの構造を把握する必要はありません。DBMS_CLOUDがファイルを調べてORR、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オブジェクト・ストアにアクセスするための資格証明の作成は必要ありません。詳細は、リソース・プリンシパルの使用による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または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"}'
     );
    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で検出した最初のORR、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(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を使用して表にロードできます。

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

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

ORC、ParquetまたはAvroソース・ファイルを使用した外部データの問合せおよびテキスト列サイズの明示的設定

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

デフォルトでは、テキスト列サイズはMAX_STRING_SIZEの値に基づいて設定されます。

この例のソース・ファイル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オブジェクト・ストアにアクセスするための資格証明の作成は必要ありません。詳細は、リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセスを参照してください。

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

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

  2. プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用して、ソース・ファイルに基づいてORR、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を指定します。

      オプションmaxvarchar(値extended)は、テキスト列が拡張文字列サイズのAutonomous Databaseインスタンスでvarchar(32767)として作成されることを指定します。指定可能な値は、varchar(4000)を指定したstandardvarchar(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で検出した最初のORR、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(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オプションmaxvarcharstandardとして指定すると、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)
    

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

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