専用Exadataインフラストラクチャ上のAutonomous AI Databaseを使用した外部データの問合せ

専用Exadataインフラストラクチャ上のAutonomous AI Databaseでデータの問合せおよび検証を行うには、パッケージとツールについて説明します。

外部データはデータベースによって管理されませんが、DBMS_CLOUDプロシージャを使用して外部データを問い合せるすることはできます。外部データの問合せはデータベース表の問合せほど高速ではありませんが、この方法を使用すると、外部ソース・ファイルおよび外部データの問合せの実行をすばやく開始できます。

DBMS_CLOUDプロシージャを使用すると、外部表の外部ソース・ファイル内にあるデータを検証できるため、問題を特定して、データを使用する前に外部表のファイルを修正するか、無効なデータを除外することができます。

ノート: ADMINユーザーを使用していない場合は、ユーザーが実行する必要がある操作に対する必要な権限を持っていることを確認してください。詳細は、データベース・ユーザー権限の管理を参照してください。

外部データの問合せ

クラウドのファイルのデータを問い合せるために、まず、オブジェクト・ストレージ資格証明をAutonomous AI Databaseに格納してから、PL/SQLプロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用して外部表を作成する必要があります。

この例のソース・ファイルchannels.txtには、次のデータが含まれます:

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others

DBMS_CREDENTIAL.CREATE_CREDENTIALプロシージャを使用して、クラウド・オブジェクト・ストレージ資格証明を格納します。たとえば:
```
 BEGIN
   DBMS_CREDENTIAL.CREATE_CREDENTIAL(
     credential_name => 'DEF_CRED_NAME',
     username => 'adb_user@oracle.com',
     password => 'password'
   );
 END;
 /
```
usernameおよびpasswordに指定する値は、使用しているクラウド・オブジェクト・ストレージ・サービスによって異なります:
- Oracle Cloud Infrastructure Object Storage: usernameはOracle Cloud Infrastructureのユーザー名で、passwordはOracle Cloud Infrastructure認証トークンです。認証トークンの作業を参照してください。
- Oracle Cloud Infrastructure Object Storage Classic: usernameはOracle Cloud Infrastructure Classicのユーザー名で、passwordはOracle Cloud Infrastructure Classicのパスワードです。
この操作によって、資格証明が暗号化された形式でデータベースに格納されます。資格証明には任意の名前を使用できます。オブジェクト・ストアの資格証明を変更しないかぎり、このステップが必要なのは1回のみです。資格証明を格納した後は、すべてのデータ・ロードで同じ資格証明名を使用できます。
プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用して、ソース・ファイルの上に外部表を作成します。

プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEは、サポートされているクラウド・オブジェクト・ストレージ・サービスの外部ファイルをサポートします。資格証明は表レベルのプロパティであるため、外部ファイルは同じオブジェクト・ストアに存在する必要があります。

たとえば:
```
 BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
     table_name =>'CHANNELS_EXT',
     credential_name =>'DEF_CRED_NAME',
     file_uri_list =>'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/channels.txt',
     format => json_object('delimiter' value ','),
     column_list => 'CHANNEL_ID NUMBER,
     CHANNEL_DESC VARCHAR2(20),
     CHANNEL_CLASS VARCHAR2(20)'
  );
 END;
 /
```
パラメータは次のとおりです:
- table_name: 外部表の名前です。
- credential_name: 前のステップで作成された資格証明の名前です。
- file_uri_list: 問い合せるソース・ファイルのカンマ区切りリストです。
  
  この例では、file_uri_listは、us-phoenix-1リージョンにあるmybucketバケット内のchannels.txtファイルを指定するOracle Cloud Infrastructure Swift URIです。(idthydc0kinrは、バケットが存在するオブジェクト・ストレージ・ネームスペースです。) サポートされているURIフォーマットの詳細は、クラウド・オブジェクト・ストレージのURIフォーマットを参照してください。
- format: ソース・ファイルのフォーマットを説明するために指定できるオプションを定義します。指定できるフォーマット・オプションの詳細は、フォーマット・パラメータを参照してください。
- column_list: ソース・ファイル内の列定義のカンマ区切りリストです。
これで、前のステップで作成した外部表に対して問合せを実行できるようになります。たとえば:
```
 SELECT count(*) FROM channels_ext;
```
デフォルトでは、データベースは、外部データ・ファイルのすべての行が有効であり、ターゲット・データのタイプ定義とファイルのフォーマット定義の両方に一致することを想定します。指定したフォーマット・オプションに一致しない行がソース・ファイル内にある場合、問合せはエラーを報告します。これらのエラーを抑制するには、rejectlimitなどのformatパラメータ・オプションを使用できます。別の方法として、作成した外部表を検証して、エラー・メッセージおよび却下された行を確認し、それに応じてフォーマット・オプションを変更することもできます。詳細は、外部データの検証を参照してください。

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

外部表メタデータ列

外部表メタデータは、問合せの実行時にデータの発生元を特定するのに役立ちます。

DBMS_CLOUD.CREATE_EXTERNAL_TABLE、DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEまたはDBMS_CLOUD.CREATE_HYBRID_PART_TABLEを使用して作成する外部表には、2つの非表示列file$pathおよびfile$nameが含まれます。これらの列は、レコードの取得元ファイルの識別に役立ちます。

file$path: オブジェクト名の先頭までのファイル・パス・テキストを指定します。
file$name: 最後の"/"に続くすべてのテキストを含め、オブジェクトの名前を指定します。

たとえば:

SELECT genre_id, name, file$name, file$path FROM ext_genre
     WHERE rownum <= 2;

genre_id      name        file$name     file$path
--------      ---------   -----------   ----------------------
1             Action      genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
2             Adventure   genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre

非表示列の詳細は、「非表示列」を参照してください。

外部データの検証

外部表を検証するには、プロシージャDBMS_CLOUD.VALIDATE_EXTERNAL_TABLEを使用します。

外部表を検証する前に、DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用して外部表を作成する必要があります。次に、DBMS_CLOUD.VALIDATE_EXTERNAL_TABLEプロシージャを使用してそれを検証します。たとえば:

BEGIN
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS_EXT' );
END;
/

このプロシージャによってソース・ファイルがスキャンされ、外部表の作成時に指定したフォーマット・オプションを使用して検証されます。

検証操作は、デフォルトでソース・ファイル内のすべての行をスキャンし、行が拒否されると停止します。行のサブセットのみを検証する場合は、rowcountパラメータを使用します。rowcountパラメータが設定されている場合、検証操作は行をスキャンし、行が拒否されるか、指定した数の行がエラーなしで検証されると停止します。

たとえば、次の検証操作は100行をスキャンし、行が拒否されるか、100行がエラーなしで検証されると停止します:

BEGIN
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100 );
END;
/

行が拒否されても検証を停止せずに、拒否されたすべての行を確認する場合は、stop_on_errorパラメータをFALSEに設定します。この場合、VALIDATE_EXTERNAL_TABLEは、すべての行をスキャンし、拒否されたすべての行をレポートします。

行のサブセットのみを検証する場合は、rowcountパラメータを使用します。rowcountが設定され、stop_on_errorがFALSEに設定されている場合、検証操作は行をスキャンし、指定した数の行が拒否されるか、指定した数の行がエラーなしで検証されると停止します。たとえば、次の例は100行をスキャンし、100行が拒否されるか、100行がエラーなしで検証されると停止します:

BEGIN
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100,
     stop_on_error => FALSE );
END;
/

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

表dba_load_operationsおよびuser_load_operationsの検証操作の結果を確認するには、データ妥当性チェックのログの表示を参照してください。

データ検証のログの表示

外部表の検証後、ロード操作表を問い合せると、検証操作の結果を確認できます:

dba_load_operations: すべての検証操作が表示されます。
user_load_operations: スキーマ内の検証操作が表示されます。

これらの表を使用して、ロード検証情報を確認できます。たとえば、user_load_operationsを問い合せるには、次のSELECT文を使用します:

SELECT table_name,owner_name,type,status,start_time,update_time,logfile_table,badfile_table
FROM user_load_operations
WHERE type = 'VALIDATE';

TABLE_NAME    OWNER_NAME  TYPE       STATUS     START_TIME     UPDATE_TIME    LOGFILE_TABLE    BADFILE_TABLE
------------- ----------  ---------- ---------- -------------- -------------  ---------------  ---------------
CHANNELS_EXT  SH          VALIDATE   COMPLETED  13-NOV-17...   13-NOV-17...   VALIDATE$21_LOG  VALIDATE$21_BAD

TYPE列でこのSQL文をWHERE句とともに使用すると、VALIDATEタイプのロード操作がすべて表示されます。

LOGFILE_TABLE列には、検証操作のログを確認するための問合せを実行できる表の名前が表示されます。たとえば、次の問合せでは、この検証操作のログが表示されます:

SELECT * FROM VALIDATE$21_LOG;

BADFILE_TABLE列には、検証中にエラーが発生した行を確認するための問合せを実行できる表の名前が表示されます。たとえば、次の問合せでは、前述の検証操作で拒否されたレコードが表示されます:

SELECT * FROM VALIDATE$21_BAD;

ログに示されているエラーおよびBADFILE_TABLEに示されている行によっては、DROP TABLEコマンドを使用して外部表を削除してから、DBMS_CLOUD.CREATE_EXTERNAL_TABLEで適切なフォーマット・オプションを指定して外部表を再作成することで、エラーを修正できる場合があります。

ノート: LOGFILE_TABLE表およびBADFILE_TABLE表は、各検証操作について2日間保存されてから、自動的に削除されます。