在专用 Exadata 基础结构上使用自治 AI 数据库查询外部数据

介绍在专用 Exadata 基础结构上使用自治 AI 数据库查询和验证数据的程序包和工具。

外部数据不是由数据库管理的；但是，您可以使用 DBMS_CLOUD 过程查询外部数据。虽然对外部数据的查询速度不如对数据库表的查询快，但您可以使用此方法快速开始对外部源文件和外部数据运行查询。

您可以使用 DBMS_CLOUD 过程来验证外部表外部源文件中的数据，以便可以确定问题并更正外部表中的数据，或者在使用数据之前排除无效数据。

注：如果不使用 ADMIN 用户，请确保该用户对用户需要执行的操作具有必要的权限。有关详细信息，请参阅管理数据库用户权限。

查询外部数据

要在云中的文件中查询数据，您需要先将对象存储身份证明存储在自治 AI 数据库中，然后使用 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 验证令牌。请参见 Working with Auth Tokens 。
- Oracle Cloud Infrastructure Object Storage Classic：username 是您的 Oracle Cloud Infrastructure Classic 用户名，password 是您的 Oracle Cloud Infrastructure Classic 密码。
此操作以加密格式将身份证明存储在数据库中。可以将任何名称用于身份证明名称。请注意，除非对象存储身份证明发生更改，否则此步骤仅需要一次。存储身份证明后，即可对所有数据加载使用相同的身份证明名称。
使用过程 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.CREATE_EXTERNAL_TABLE、DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE 或 DBMS_CLOUD.CREATE_HYBRID_PART_TABLE 创建的外部表包括两个不可见列 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 Procedure 。

请参阅“查看用于数据验证的日志”以查看表 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 表存储两天，然后自动删除。