暗黙的パーティション化を使用した外部表の問合せ

Autonomous AI Databaseでは、Hiveスタイルのパーティション化されたデータから、またはクラウド・オブジェクト・ストアに格納されている単純なフォルダ・パーティション化されたデータから、暗黙的なパーティション化された外部表を作成できます。

暗黙的パーティション化を使用した外部表について

Autonomous AI Databaseでは、DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用して、Hiveスタイルのパーティション化されたデータまたはクラウド・オブジェクト・ストアに格納されている単純なフォルダ・パーティション化されたデータから暗黙的なパーティション化された外部表を作成します。

このプロシージャに適切なオプションを渡すことによって、パーティションはソース・データから導出されます。パーティション化された外部表は、パーティション列とその値の実行時検出をサポートします。オブジェクトの追加や削除など、基礎となるオブジェクト・ストア構造の変更のランタイム検出により、DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEで必要な追加の同期プロシージャが不要になるため、メンテナンス・プロセスが簡素化されます。これにより、問合せの実行時にデータが最新になります。

暗黙的なパーティション化では、Autonomous AI Databaseは、オブジェクト・ストレージ・ソースの階層ファイル構造に基づいて、表がパーティション化される列を自動的に判別します。パーティション化スキームを明示的に宣言する必要はありません。暗黙的なパーティション化では、DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEプロシージャを使用してパーティション化された外部表を明示的に定義しなくても、パーティション表と同様のパフォーマンス上の利点が得られます。

暗黙的なパーティション化を使用して、非常に大きいデータ・セットの問合せに対する問合せレスポンス時間を最適化するために、strict_column_orderをtrueに設定して最適化を有効にできます。strict_column_orderパラメータは、implicit_partition_config形式オプションのサブオプションです。この最適化は、partition_typeがhiveの場合にのみ適用されます。暗黙的なパーティション化フォーマット・パラメータの詳細は、「Hiveフォーマット・ソース・ファイル編成を使用した外部暗黙的パーティション・データの問合せ」および「DBMS_CLOUDパッケージのフォーマット・オプション」を参照してください。

暗黙的パーティション化された外部表は、オブジェクト・ストア内のパーティション化されたオブジェクトに対して次のネーミング・スタイルをサポートします。

Hiveネーミング形式: Hiveから生成されたオブジェクト・ストレージ内のデータの形式は、次のとおりです。
```
OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet
```
例については、「Hiveフォーマット・ソース・ファイル編成を使用した外部暗黙的パーティション・データの問合せ」を参照してください
Hive以外の"Pathtail"ネーミング形式: データ・レイクでよく見られる2番目の形式は、hive形式に似ていますが、フォルダ名にはパーティション列が含まれていません。
```
OBJBUCKET/<table>/<value1>/<value2>/file.parquet
```
例については、外部暗黙的パーティション非Hiveスタイル・データの問合せを参照してください。

フォーマットの命名の詳細は、「ソース・ファイル・パーティション化による外部表について」を参照してください。

DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用すると、暗黙的なパーティション化された外部表を次の方法で作成できます。

暗黙的なパーティション化を最適化します。implicit_partition_configオプションを使用して、暗黙的なパーティション化および経過問合せ時間の最適化を有効にします。

この例では、暗黙的なパーティション化を有効にするには、partition_typeをhiveに設定します。partition_typeの有効な値はhiveのみです。最適化を有効にするには、setting strict_column_orderをtrueに設定します。partition_columnsオプションは、パーティション化される列を指定します。

たとえば:

BEGIN
dbms_cloud.create_external_table (
   table_name        => 'partitions1',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'c varchar2(100), y number, total_sales number',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["org","tenant","dbtype","year","month","day"]
                          }
                          }');
END;
/

パーティション列のリストを指定して、パーティション・タイプをハイブに設定します。

この場合、implicit_partition_typeはhiveに設定され、implicit_partition_columnsはパーティション列のリストを提供します。

たとえば:

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

パーティション列のリストを指定せずに、パーティション・タイプをハイブに設定します。

この場合、implicit_partition_typeはhiveに設定され、implicit_partition_columnsは指定されません。パーティション列は、file_uri_listで指定されたパスで'='を検索することで自動的に検出されます。column_nameは'='の左側にあり、値は右側にあります。パスにcolumn_nameが見つからない場合は、エラーがスローされます。

たとえば:
```
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   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"');
END;
/
```

タイプを指定せずにパーティション列のリストを指定します。

この場合、implicit_partition_typeは設定されず、implicit_partition_columnsは列のリストを提供します。

たとえば:

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

詳細は、「Hive形式ソース・ファイル編成を使用した外部暗黙的パーティション・データの問合せ」および「CREATE_EXTERNAL_TABLEプロシージャ」を参照してください。

Hive形式のソース・ファイル編成を使用した外部暗黙的パーティション・データの問合せ

DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用して、Hiveデータから生成されたオブジェクト・ストアのデータから暗黙的にパーティション化された外部表を作成します。

次の例のサンプル・ソース・ファイルでは、次のネーミング形式を使用します。

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

次のサンプル・ソース・ファイルについて考えてみます。

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

このサンプルHive形式で格納されたデータを含む暗黙的なパーティション化された外部表を作成するには、次の手順を実行します。

プロシージャDBMS_CLOUD.CREATE_CREDENTIALを使用して、オブジェクト・ストア資格証明を保存します。

たとえば:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/
```
リソース・プリンシパル資格証明を有効にする場合、Oracle Cloud Infrastructureオブジェクト・ストアにアクセスするための資格証明の作成は必要ありません。詳細は、リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセスを参照してください。

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

様々なオブジェクト・ストレージ・サービスのusernameおよびpasswordパラメータの詳細は、「CREATE_CREDENTIALプロシージャ」を参照してください。
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEプロシージャを使用して、ソース・ファイルの上に暗黙的なパーティション化された外部表を作成します。
1. ケース1: 暗黙的なパーティション化を有効にします。
  
  この例では、フォーマット・オプションimplicit_partition_columnsが指定されていないため、パーティション列は自動的に検出され、フォーマット・オプションimplicit_partition_typeをhiveに設定することで暗黙的なパーティション化が有効になります。
  
  ただし、列のリストが指定されている場合は、それらの列がパーティション列として使用され、Autonomous AI Databaseでは列の検出が試行されません。
  
  パーティション列を検出するために、Autonomous AI Databaseは、file_uri_listで指定されたパスの先頭から「= 」の検索を開始します。見つかった場合、最後の'/'までの'='の左部分が列(例: "country")として、最初の'/'が値(例: "USA")になるまで'='の右部分とみなされます。検索は、1番目のパーティション値の後に続く'/'の後、パス内の2番目の'='を検出するまで、'='に対して続行されます。
```
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   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"');
END;
/
```
  パラメータは次のとおりです。
  - table_name: 外部表の名前です。
  - credential_name: 前のステップで作成された資格証明の名前です。
  - file_uri_list: ソース・ファイルURIのカンマ区切りリストです。このリストには、次の2つのオプションがあります:
    - ワイルドカードを使用せずに個々のファイルURIのカンマ区切りリストを指定します。
    - ワイルドカードを含む単一のファイルURIを指定します(ワイルドカードは最後のスラッシュ"/"の後にのみ指定できます)。文字"*"は複数の文字のワイルドカードとして使用でき、文字"?"は1つの文字のワイルドカードとして使用できます。
    この例では、namespace-stringはOracle Cloud Infrastructureオブジェクト・ストレージ・ネームスペースで、bucketnameはバケット名です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。
  - column_list: 外部表の列名およびデータ型のカンマ区切りリスト。リストには、データ・ファイル内の列と、オブジェクト名から導出された列(file_uri_listで指定されたファイル・パスの名前から)が含まれます。
    
    データ・ファイルが構造化ファイル(Parquet、AvroまたはORC)の場合、column_listは不要です。
  - format: ソース・ファイルのフォーマットを説明するために指定できるオプションを定義します。implicit_partition_typeオプションは、データ形式タイプをhiveとして指定します。
    
    ソースファイル内のデータが暗号化されている場合は、encryption形式オプションを指定してデータを復号化します。データの復号化の詳細は、オブジェクト・ストレージからのインポート中のデータの復号化を参照してください。
    
    詳細は、DBMS_CLOUDパッケージ・フォーマット・オプションを参照してください。
2. ケース2: 最適化された暗黙的パーティション化
  
  この例では、implicit_partition_configフォーマット・オプションを指定して暗黙的パーティション化を有効にし、strict_column_orderをtrueに設定して最適化された暗黙的パーティション化を有効にします。
  
  partition_columnsはパーティション列のリストを指定するため、これらの列はパーティション列として使用され、Autonomous AI Databaseは列の検出を試行しません。
```
BEGIN
dbms_cloud.create_external_table (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   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_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["country","year","month","day"]
                          }
                          }');
END;
/
```
  フォーマット・パラメータは次のとおりです。
  - format: ソース・ファイルのフォーマットを説明するために指定できるオプションを定義します。implicit_partition_configオプションには、次のサブオプションがあります。
    - partition_type
      
      許容される値は"hive"のみです。デフォルトは、hive以外のファイル名パスです。partition_typeが指定されていない場合は、クライアント・エラーが返されます。
    - partition_columns
      
      パーティション化された列のリスト。
    - strict_column_order
      
      最適化された暗黙的パーティション化を有効にするには、trueに設定します。オブジェクト・ストア・パスが変更されず、接頭辞が欠落していないことがわかっている場合にのみ、trueに設定します。falseに設定するか、省略して無効化します。
    ソースファイル内のデータが暗号化されている場合は、encryption形式オプションを指定してデータを復号化します。データの復号化の詳細は、オブジェクト・ストレージからのインポート中のデータの復号化を参照してください。
    
    詳細は、DBMS_CLOUDパッケージ・フォーマット・オプションを参照してください。
パラメータの詳細は、『CREATE_EXTERNAL_TABLEプロシージャ』を参照してください。サポートされるクラウド・オブジェクト・ストレージ・サービスの詳細は、「DBMS_CLOUD URI形式」を参照してください。
これで、前のステップで作成した外部パーティション表に対して問合せを実行できます。

Autonomous AI Databaseでは、外部パーティション表のパーティション化情報を利用して、問合せがオブジェクト・ストア内の関連データ・ファイルにのみアクセスするようにしています。

たとえば:
```
SELECT product, units FROM mysales WHERE year='2024' and month='02'
```
このSQL文は、2024年の月2日のパーティションのデータのみを問い合せます。

strict_column_orderオプションを使用したオブジェクト・ストア問合せ計画の最適化

非常に多くのファイルおよびサブフォルダを持つオブジェクト・ストア・フォルダを問合せがターゲットとする場合、計画およびリスト・フェーズは、データが実際にスキャンされる前にプライマリ・コストになる可能性があります。これは、パスにパーティション値が埋め込まれているHiveスタイルのフォルダ・レイアウトに共通です。

ノート

ノート: - strict_column_order設定はデフォルトでは無効です。パス・レイアウトに一貫性がある場合にのみ、有効にします。

フォルダ規則が変更された場合(パーティション列の追加、削除、並べ替えなど)、partition_columnsオプションを更新する必要があり、このオプションを無効にする必要がある場合があります。

次の方法でstrict_column_orderオプションを有効にします。

データでHive形式のパーティション・パスが使用され、パーティション列が常に固定された一貫性のある順序でセグメントが欠落していない場合は、次の設定で最適化を有効にします。

strict_column_order = true  (with partition_type = "hive")

データベースは、すでに定義されているパーティションの順序に従って、不要なディレクトリをスキップできます。これは、すべてのオブジェクトを一覧表示する必要がなく、大規模なデータセットの計画時間を大幅に短縮できることを意味します。

strict_column_orderオプションの使用に関するノート:

パスは、Hiveのネーミングおよび順序に従います。たとえば:
```
.../country=US/year=2025/month=09/...
```
パーティション列は、接頭辞の順序変更やスキップを行わずに、固定セットおよび順序を維持します。
非常に多数のオブジェクトを含むフォルダのプランタイム・リストを最適化する必要があります。
すべての接頭辞が存在するわけではないデータセットでは、このオプションを使用できません。たとえば、フォルダの中にはyear= を含むものとmonth= で始まるものがあります。

例: DBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用したHiveスタイル・パーティションのstrict_column_orderオプションの使用

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name        => 'sales_xt',
    credential_name   => 'MY_CREDENTIAL',
    file_uri_list     => 'https://objectstorage.example.com/n/tenant/b/lake/o/sales/',
    -- Data is stored as: .../country=US/year=2025/month=09/*.parquet
    column_list       => 'product       VARCHAR2(100),
                          units         NUMBER,
                          amount        NUMBER,
                          country       VARCHAR2(30),
                          year          NUMBER,
                          month         NUMBER',
    format            => '{
      "type": "parquet",
      "implicit_partition_config": {
        "partition_type": "hive",
        "partition_columns": ["country","year","month"],
        "strict_column_order": true
      }
    }'
  );
END;
/

計画ロジックによって、関連のない最上位レベルのパス(他の国や年に属するパスなど)がリストされないことを確認します。

先頭のパーティションを制約する選択的問合せを実行します。次に例を示します。
```
SELECT COUNT(*) FROM sales_xt WHERE country = ''US'' AND year = 2025;
```
次と
```
strict_column_order = true
```
.
レイアウトの仮定が満たされない場合は、オプションを無効化して再試行してください。

外部暗黙的パーティション非Hiveスタイル・データの問合せ

DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用して、Hive以外のデータから生成されたオブジェクト・ストレージのデータから暗黙的にパーティション化された外部表を作成します。

次の例のサンプル・ソース・ファイルでは、次のネーミング形式を使用します。

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

次のサンプル・ソース・ファイルについて考えてみます。

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

このサンプルHive形式で格納されたデータを含む暗黙的なパーティション化された外部表を作成するには、次の手順を実行します。

プロシージャDBMS_CLOUD.CREATE_CREDENTIALを使用して、オブジェクト・ストア資格証明を保存します。

たとえば:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/
```
リソース・プリンシパル資格証明を有効にする場合、Oracle Cloud Infrastructureオブジェクト・ストアにアクセスするための資格証明の作成は必要ありません。詳細は、リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセスを参照してください。

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

様々なオブジェクト・ストレージ・サービスのusernameおよびpasswordパラメータの詳細は、「CREATE_CREDENTIALプロシージャ」を参照してください。
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEプロシージャを使用して、ソース・ファイルの上に暗黙的なパーティション化された外部表を作成します。

この例では、フォーマット・オプションimplicit_partition_columnsでパーティション列を指定することで、暗黙的なパーティション化が有効になります。フォルダ名にはパーティション列が含まれないため、file_uri_listで指定されたパスのパーティション値は、フォーマット・オプションimplicit_partition_columnsで列の明示的なリストが指定されている場合にのみ検出できます。パスの各列値を検出するには、列の順序が値の順序と同じである必要があります。

パーティション列が自動的に検出されます。
```
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   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"]}');
END;
/
```
パラメータは次のとおりです。
- table_name: 外部表の名前です。
- credential_name: 前のステップで作成された資格証明の名前です。
- file_uri_list: ソース・ファイルURIのカンマ区切りリストです。このリストには、2つのオプションがあります。
  - ワイルドカードを使用せずに個々のファイルURIのカンマ区切りリストを指定します。
  - ワイルドカードを含む単一のファイルURIを指定します(ワイルドカードは最後のスラッシュ"/"の後にのみ指定できます)。文字"*"は複数の文字のワイルドカードとして使用でき、文字"?"は1つの文字のワイルドカードとして使用できます。
- column_list: 外部表の列名とデータ型のカンマ区切りリスト。リストには、データ・ファイル内の列と、オブジェクト名から導出された列(file_uri_listで指定されたファイル・パスの名前から)が含まれます。
  
  データ・ファイルが構造化ファイル(Parquet、AvroまたはORC)の場合、column_listは不要です。
- format: ソース・ファイルのフォーマットを説明するために指定できるオプションを定義します。implicit_partition_typeオプションは設定されていません。implicit_partition_columnsが指定されているため、タイプは自動的に非hiveとして検出されます。
  
  ソースファイル内のデータが暗号化されている場合は、encryption形式オプションを指定してデータを復号化します。データの復号化の詳細は、オブジェクト・ストレージからのインポート中のデータの復号化を参照してください。
  
  詳細は、DBMS_CLOUDパッケージ・フォーマット・オプションを参照してください。
この例では、namespace-stringはOracle Cloud Infrastructureオブジェクト・ストレージ・ネームスペースで、bucketnameはバケット名です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。

パラメータの詳細は、『CREATE_EXTERNAL_TABLEプロシージャ』を参照してください。

サポートされるクラウド・オブジェクト・ストレージ・サービスの詳細は、「DBMS_CLOUD URI形式」を参照してください。
これで、前のステップで作成した外部パーティション表に対して問合せを実行できます。

Autonomous AI Databaseでは、外部パーティション表のパーティション化情報を利用して、問合せがオブジェクト・ストア内の関連データ・ファイルにのみアクセスするようにしています。

たとえば:
```
SELECT product, units FROM mysales WHERE year='2024'
```
このSQL文は、2024年のパーティションのデータのみを問い合せます。

Oracle Cloud Infrastructureドキュメント

暗黙的パーティション化を使用した外部表の問合せ

暗黙的パーティション化を使用した外部表について

Hive形式のソース・ファイル編成を使用した外部暗黙的パーティション・データの問合せ

strict_column_orderオプションを使用したオブジェクト・ストア問合せ計画の最適化

例: DBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用したHiveスタイル・パーティションのstrict_column_orderオプションの使用

外部暗黙的パーティション非Hiveスタイル・データの問合せ