在專用 Exadata 基礎架構上使用 Autonomous Database 查詢外部資料

描述使用 Autonomous Database on Dedicated Exadata Infrastructure 查詢和驗證資料的套裝軟體和工具。

外部資料不是由資料庫管理；不過，您可以使用 DBMS_CLOUD 程序來查詢外部資料。雖然對外部資料的查詢速度不如對資料庫表格的查詢快，但您可以使用此方法來快速開始對外部來源檔案和外部資料執行查詢。

您可以使用 DBMS_CLOUD 程序來驗證外部表格之外部來源檔案中的資料，以便識別問題，並在使用資料之前更正外部表格中的資料或排除無效的資料。

附註：

如果您不是使用 ADMIN 使用者，請確定使用者具有使用者需要執行之作業的必要權限。請參閱管理資料庫使用者權限瞭解詳細資訊。

查詢外部資料

若要查詢雲端檔案中的資料，您必須先將物件儲存證明資料儲存在 Autonomous 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

1. 使用 DBMS_CREDENTIAL.CREATE_CREDENTIAL 程序儲存您的雲端物件儲存證明資料。例如：

   BEGIN
     DBMS_CREDENTIAL.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => '[e-mail]',
       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 密碼。

   此作業會以加密格式將證明資料儲存在資料庫中。您可以使用任何證明資料名稱。請注意，除非您的物件儲存證明資料變更，否則此步驟僅需要一次。儲存證明資料之後，就可以在所有資料載入使用相同的證明資料名稱。

2. 使用 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 是一個 Oracle Cloud Infrastructure Swift URI，可在 us-phoenix-1 區域的 mybucket 儲存桶中指定 channels.txt 檔案。(idthydc0kinr 是儲存桶所在的物件儲存命名空間。)如需有關支援 URI 格式的資訊，請參閱雲端物件儲存 URI 格式。

   • format：定義可以指定來描述來源檔案格式的選項。如需有關可指定格式選項的資訊，請參閱格式參數。

   • column_list：是來源檔案中以逗號分隔的資料欄定義清單。

   您現在可以對在上一個步驟建立的外部表格執行查詢。舉例而言：

   SELECT count(*) FROM channels_ext;

   依照預設，資料庫會預期外部資料檔中的所有資料列均有效，且符合目標資料類型定義以及檔案的格式定義。如果來源檔案中有任何不符合您指定之格式選項的資料列，查詢就會報告錯誤。您可以使用 format 參數選項 (例如 rejectlimit) 來抑制這些錯誤。或者，您也可以驗證您建立的外部表格，以查看錯誤訊息和拒絕的資料列，以便您相應地變更格式選項。請參閱驗證外部資料以瞭解詳細資訊。

   如需有關參數的詳細資訊，請參閱 CREATE_EXTERNAL_TABLE 程序。

驗證外部資料

若要驗證外部表格，請使用程序 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; 
/

請參閱 VALIDATE_EXTERNAL_TABLE 程序，瞭解有關 DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE 參數的詳細資訊。

請參閱檢視資料驗證的日誌，查看 dba_load_operations 和 user_load_operations 表格中驗證作業的結果。

檢視資料驗證的日誌

驗證外部表格之後，您可以透過查詢載入作業表格來查看驗證作業的結果：

• dba_load_operations：顯示所有驗證作業。

• user_load_operations：顯示綱要中的驗證作業。

您可以使用這些表格來檢視載入驗證資訊。例如，使用此 SELECT 敘述句來查詢 user_load_operations：

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

將此 SQL 敘述句與 TYPE 資料欄上的 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 表格兩天，然後自動移除。

---

標題與著作權資訊

Copyright ©2021,2024,

Oracle 和 (或) 其關係企業。