使用专用 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 验证令牌。请参见 Working with Auth Tokens 。

   • 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 格式的信息，请参见 Cloud Object Storage URI Formats 。

   • format：定义可用于描述源文件格式的选项。有关可以指定的格式选项的信息，请参阅格式参数。

   • column_list：是源文件中列定义的逗号分隔列表。

   现在，您可以对上一步中创建的外部表运行查询。例如：

   SELECT count(*) FROM channels_ext;

   默认情况下，数据库要求外部数据文件中的所有行都有效，并且与目标数据类型定义以及文件的格式定义匹配。如果源文件中存在与指定的格式选项不匹配的任何行，查询将报告错误。可以使用 format 参数选项（如 rejectlimit）隐藏这些错误。作为替代方案，您还可以验证创建的外部表以查看错误消息和拒绝的行，以便可以相应地更改格式选项。有关详细信息，请参阅验证外部数据。

   有关参数的详细信息，请参见CREATE_EXTERNAL_TABLE Procedure 。

验证外部数据

要验证外部表，请使用过程 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 Procedure 。

有关验证操作的结果，请参见 View Logs for Data Validation （在表 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 和/或其关联公司。