从云中的文件加载数据

PL/SQL 程序包 DBMS_CLOUD 支持将数据从云中的文件加载到在专用 Exadata 基础结构上的自治 AI 数据库中创建的表中。

可以使用 DBMS_CLOUD 提供的以下 PL/SQL 过程从不同的文件格式加载数据：

从文件加载数据之前，请确保：

程序包 DBMS_CLOUD 支持从以下云服务中的文件加载：Oracle Cloud Infrastructure Object Storage、Oracle Cloud Infrastructure Object Storage Classic、Azure Blob Storage 和 Amazon S3。

创建身份证明

了解如何使用 DBMS_CLOUD.CREATE_CREDENTIAL 过程存储云对象存储身份证明。

使用任何数据库工具（例如 SQL*Plus、SQL Developer 或 Database Actions）（基于 Web 的 SQL Developer 工具）执行 DBMS_CLOUD.CREATE_CREDENTIAL 过程。例如：

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@oracle.com',
    password => 'password'
  );
END;
/

您为 username 和 password 提供的值取决于您使用的云对象存储服务：

此操作以加密格式将身份证明存储在数据库中。可以将任何名称用于身份证明名称。请注意，除非对象存储身份证明发生更改，否则此步骤仅需要一次。存储身份证明后，即可对所有数据加载使用相同的身份证明名称。

从文本文件加载数据

了解如何使用 DBMS_CLOUD.COPY_DATA 过程将数据从云中的文本文件加载到自治 AI 数据库。

此示例中的源文件 channels.txt 具有以下数据：

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

  1. 使用 DBMS_CREDENTIAL.CREATE_CREDENTIAL 过程存储云对象存储身份证明。有关详细信息，请参阅创建身份证明。</span>

  2. 创建将包含数据的表。例如：

     CREATE TABLE CHANNELS
   (channel_id CHAR(1),
     channel_desc VARCHAR2(20),
     channel_class VARCHAR2(20)
   );
 /

  3. 使用过程 DBMS_CLOUD.COPY_DATA 将数据加载到表中。例如：

     BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    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 ',')
  );
 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：定义指定用于描述源文件格式的选项。有关您可以指定的格式选项的信息，请参阅格式参数。

将分隔文档的 JSON 文件加载到集合中

了解如何使用 DBMS_CLOUD.COPY_DATA 过程将带分隔符文档的 JSON 文件加载到自治 AI 数据库中的集合中。

此示例从行分隔的文件加载 JSON 值并使用 JSON 文件 myCollection.json。每个值（每行）都将作为单个文档加载到自治 AI 数据库的集合中。

下面是此类文件的示例。它有三行，每行一个对象。其中每个对象都将作为单独的 JSON 文档加载。

{ "name" : "apple", "count": 20 }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }

过程

  1. 使用 DBMS_CLOUD.CREATE_CREDENTIAL 过程存储云对象存储身份证明。有关详细信息，请参阅创建身份证明。

  2. 使用过程 DBMS_CLOUD.COPY_DATA 将数据加载到集合中。例如：

     BEGIN 
   DBMS_CLOUD.COPY_COLLECTION(
     collection_name =>'fruit',
     credential_name =>'DEF_CRED_NAME',
     file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/fruit_bucket/o/myCollection.json',
     format => json_object('recorddelimiter' value '''\n''')
   );
 END;
 /

    参数包括：

    • collection_name：是目标集合的名称。

    • credential_name：是在上一步中创建的身份证明的名称。

    • file_uri_list：是要加载的源文件的逗号分隔列表。

      在此示例中，file_uri_list 是 Oracle Cloud Infrastructure Swift URI，用于指定 us-phoenix-1 区域中 mybucket 存储桶中的 myCollection.json 文件。有关支持的 URI 格式的信息，请参见 Cloud Object Storage URI Formats 。

    • format：定义指定用于描述源文件格式的选项。加载 JSON 数据时支持格式选项 characterset, compression, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, unpackarray。指定的其他任何格式都将导致错误。有关您可以指定的格式选项的信息，请参阅格式参数。

将一组 JSON 文档加载到集合中

了解如何使用 DBMS_CLOUD.COPY_COLLECTION 过程将一系列 JSON 文档加载到自治 AI 数据库中的集合中。

此示例使用 JSON 文件 fruit_array.json。下面显示了文件 fruit_array.json 的内容：

[{"name" : "apple", "count": 20 },
 {"name" : "orange", "count": 42 },
 {"name" : "pear", "count": 10 }]

过程

  1. 使用 DBMS_CLOUD.CREATE_CREDENTIAL 过程存储云对象存储身份证明。有关详细信息，请参阅创建身份证明。

  2. 使用过程 DBMS_CLOUD.COPY_DATA 将数据加载到集合中。例如：

     BEGIN 
   DBMS_CLOUD.COPY_COLLECTION(    
     collection_name => 'fruits',    
     credential_name => 'DEF_CRED_NAME',    
     file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/json/o/fruit_array.json',
     format => '{"recorddelimiter" : "0x''01''", "unpackarrays" : "TRUE", "maxdocsize" : "10240000"}'
   );
 END;
 /

    在此示例中，您将加载占用整个文件的单个 JSON 值。因此，无需指定记录分隔符。要指示没有记录分隔符，可以使用输入文件中未出现的字符。例如，可以使用值 "0x''01''"，因为此字符不直接出现在 JSON 文本中。

    当格式值的 unpackarrays 参数设置为 TRUE 时，文档数组将作为单个文档而不是整个数组加载。但是，数组元素的解压缩仅限于单个级别。如果文档中存在嵌套数组，则不会解压缩这些数组。

    参数包括：

    • collection_name：是目标集合的名称。

    • credential_name：是在上一步中创建的身份证明的名称。

    • file_uri_list：是要加载的源文件的逗号分隔列表。

      在此示例中，file_uri_list 是 Oracle Cloud Infrastructure Swift URI，用于指定 us-phoenix-1 区域中 mybucket 存储桶中的 myCollection.json 文件。有关支持的 URI 格式的信息，请参见 Cloud Object Storage URI Formats 。

    • format：定义指定用于描述源文件格式的选项。加载 JSON 数据时支持格式选项 characterset, compression, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, unpackarray。指定的其他任何格式都将导致错误。有关您可以指定的格式选项的信息，请参阅格式参数。

    使用格式选项 unpackarrays 加载带有 DBMS_CLOUD.COPY_COLLECTION 的 fruit_array.json 可使过程识别源中的数组值。因此，数据不是像默认情况下那样以单个文档的形式加载数据，而是以数组中的每个值作为单个文档加载到集合 fruits 中。

将 JSON 数据复制到现有表中

使用 DBMS_CLOUD.COPY_DATA 将云中的 JSON 数据加载到表中。

此示例中的源文件是 JSON 数据文件。

过程

  1. 使用过程 DBMS_CLOUD.CREATE_CREDENTIAL 存储对象存储身份证明。例如：

     SET DEFINE OFF
 BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DEF_CRED_NAME',
     username => 'adb_user@example.com',
     password => 'password'
   );
 END;
 /

    此操作以加密格式将身份证明存储在数据库中。可以将任何名称用于身份证明名称。请注意，除非对象存储身份证明发生更改，否则此步骤仅需要一次。存储身份证明后，即可对所有数据加载使用相同的身份证明名称。

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

  2. 使用过程 DBMS_CLOUD.COPY_DATA 将 JSON 数据加载到现有表中。

    例如：

     CREATE TABLE WEATHER2
     (WEATHER_STATION_ID VARCHAR2(20),
      WEATHER_STATION_NAME VARCHAR2(50));
 /

 BEGIN
   DBMS_CLOUD.COPY_DATA(
       table_name      => 'WEATHER2',
       credential_name => 'DEF_CRED_NAME',
       file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/jsonfiles*',
       format          =>  JSON_OBJECT('type' value 'json', 'columnpath' value '["$.WEATHER_STATION_ID",
           "$.WEATHER_STATION_NAME"]')
     );
 END;
 /

    参数包括：

    • table_name：是目标表的名称。

    • credential_name：是在上一步中创建的身份证明的名称。

    • file_uri_list：是要加载的源文件的逗号分隔列表。您可以在 URI 中的文件名中使用通配符。字符 "*" 可以用作表示多个字符的通配符，字符 "?" 可以用作表示单个字符的通配符。

    • format：对于包含 JSON 数据的 DBMS_CLOUD.COPY_DATA，type 为 json。指定其他格式值以定义用于描述 JSON 源文件格式的选项。有关更多信息，请参见 DBMS_CLOUD Package Format Options 。

    在此示例中，namespace-string 是 Oracle Cloud Infrastructure 对象存储名称空间，bucketname 是存储桶名称。有关详细信息，请参阅了解对象存储名称空间。

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

监视和排除数据加载故障

使用 PL/SQL 程序包 DBMS_CLOUD 执行的所有数据加载操作都记录在表 dba_load_operations 和 user_load_operations 中：

查询这些表可查看有关正在进行的和已完成的数据加载的信息。例如，在 TYPE 列上使用带有 WHERE 子句谓词的 SELECT 语句，显示类型为 COPY 的加载操作：

SELECT table_name, owner_name, type, status, start_time, update_time, logfile_table, badfile_table
  FROM user_load_operations WHERE type = 'COPY';
TABLE_NAME OWNER_NAME  TYPE   STATUS     START_TIME                            UPDATE_TIME                          LOGFILE_TABLE   BADFILE_TABLE
---------- ----------- ------- ---------- ---------------------- --------------------- --------------- ------------- ------------- -------------
CHANNELS   SH          COPY   COMPLETED  04-MAR-21 07.38.30.522711000 AM GMT    04-MAR-21 07.38.30.522711000 AM GMT  COPY$1_LOG     COPY$1_BAD

LOGFILE_TABLE 列显示您可以查询的表的名称，以便查看加载操作的日志。例如，以下查询显示加载操作的日志：

select * from COPY$21_LOG;

BADFILE_TABLE 列显示可以查询的表的名称，以查看加载期间出现错误的行。例如，以下查询显示加载操作的已拒绝记录：

select * from COPY$21_BAD;

根据日志中显示的错误以及指定的 BADFILE_TABLE 表中显示的行，可以通过在 DBMS_CLOUD.COPY_DATA 中指定正确的格式选项来更正错误。

注：LOGFILE_TABLE 和 BADFILE_TABLE 表为每个加载操作存储两天，然后自动删除。

dbmscloud-for-objects-and-files.md#GUID-CEC0CA63-B77F-4D64-B70F-1E8476AE3ED6

有关清除 user_load_operations 表的信息，请参见 DELETE_ALL_OPERATIONS Procedure 。

表示扩展标量值的文本 JSON 对象

本机二进制 JSON 数据（OSON 格式）通过添加与 SQL 类型相对应且不属于 JSON 标准的标量类型（如日期）来扩展 JSON 语言。Oracle Database 还支持使用表示 JSON 标量值（包括此类非标准值）的文本 JSON 对象。

从包含此类扩展对象的文本 JSON 数据创建本机二进制 JSON 数据时，可以选择将其与相应的（本机二进制）JSON 标量值替换。

扩展对象的示例为 {"$numberDecimal":31}。它表示非标准类型 decimal number 的 JSON 标量值，当解释为此类值时，将以本机二进制格式替换为小数。

例如，使用 JSON 数据类型构造函数 JSON 时，如果使用关键字 EXTENDED，则文本输入中可识别的扩展对象将替换为本机二进制 JSON 结果中的对应标量值。如果不包含关键字 EXTENDED，则不会发生此类替换；文本扩展 JSON 对象只是按原样转换为本机二进制格式的 JSON 对象。

相反，当使用 SQL/JSON 函数 json_serialize 将二进制 JSON 数据序列化为文本 JSON 数据（VARCHAR2、CLOB 或 BLOB）时，可以使用关键字 EXTENDED 将（本机二进制）JSON 标量值替换为相应的文本扩展 JSON 对象。

注：如果您使用的数据库是 Oracle Autonomous AI Database，则可以使用 PL/SQL 过程 DBMS_CLOUD.copy_collection 从包含 JSON 数据的文件（例如由公用 NoSQL 数据库（包括 Oracle NoSQL Database）生成的文档集合）创建 JSON 文档集合。

如果使用 ejson 作为过程的 type 参数的值，则输入文件中的已识别的扩展 JSON 对象将替换为生成的本机二进制 JSON 集合中的对应标量值。在另一个方向上，可以将函数 json_serialize 与关键字 EXTENDED 一起使用生成的文本 JSON 数据中的扩展 JSON 对象替换标量值。

以下是扩展对象的两个主要用例：

出于交换目的，您可以从公用 NoSQL 数据库（包括 Oracle NoSQL Database）生成的文件中摄取 JSON 数据，并将扩展对象转换为本机二进制 JSON 标量。另一方面，您可以将本机二进制 JSON 数据导出为文本数据，并将 Oracle 特定的标量 JSON 值替换为相应的文本扩展 JSON 对象。

提示：作为检验示例，请考虑将原生 JSON 数据串行化所导致的对象（如 {"dob" : "2000-01-02T00:00:00"}）。"2000-01-02T00:00:00" 是序列化类型为 date 的本机二进制值的结果，还是本机二进制值只是一个字符串？将 json_serialize 与关键字 EXTENDED 一起使用可以让您知道。

扩展对象字段到标量 JSON 类型的映射通常为多对一：可以将多种类型的扩展 JSON 对象映射到给定的标量值。例如，扩展的 JSON 对象 {"$numberDecimal":"31"} 和 {"$numberLong:"31"} 都转换为 JSON 语言标量类型编号的值 31，项方法 type() 为这些 JSON 标量中的每个标量返回 "number"。

项方法 type() 报告其目标值的 JSON 语言标量类型（作为 JSON 字符串）。有些标量值在内部是可区分的，即使它们具有相同的标量类型。这通常允许函数 json_serialize（带有关键字 EXTENDED）重构原始扩展 JSON 对象。通过使用不同的 SQL 类型实现这些标量值，或者通过将它们与从中派生的扩展 JSON 对象类型进行标记，可以在内部区分这些标量值。

当 json_serialize 重构原始扩展 JSON 对象时，结果并不总是与原始对象在文本上相同，但它始终是在语义上等效的。例如，{"$numberDecimal":"31"} 和 {"$numberDecimal":31} 在语义上是等效的，即使字段值在类型（字符串和数字）上有所不同。它们将被转换为相同的内部值，并且每个值都标记为从 $numberDecimal 扩展对象（相同的标记）派生。但是，序列化时，结果为 {"$numberDecimal":31}。 Oracle 始终对字段值使用最直接相关的类型，在本例中为标量类型编号的 JSON 语言值 31。

下表显示了所使用的各种类型之间的对应关系。它映射了 (1) 用作输入的扩展对象类型，(2) 项方法 type() 报告的类型，(3) 内部使用的 SQL 类型，(4) 函数 json_serialize 用作输出的标准 JSON 语言类型，以及 (5) 指定关键字 EXTENDED 时 json_serialize 输出扩展对象的类型。

扩展对象类型（输入） Oracle JSON 标量类型（由 type() 报告） SQL 标量类型 标准 JSON 标量类型（输出） 扩展对象类型（输出）
值为 JSON 编号的 $numberDouble、表示数字的字符串或以下字符串之一："Infinity"、"-Infinity"、"Inf"、"-Inf"、"Nan"（请参见脚注 1） 双精度 BINARY_DOUBLE 数字 值为 JSON 编号的 $numberDouble 或以下字符串之一："Inf"、"-Inf"、"Nan"（请参见脚注 2）
$numberFloat，其值与 $numberDouble 的值相同 浮点型 BINARY_FLOAT 数字 $numberFloat，其值与 $numberDouble 的值相同
$numberDecimal，其值与 $numberDouble 的值相同 数字 NUMBER 数字 $numberDecimal，其值与 $numberDouble 的值相同
$numberInt，值为带符号的 32 位整数或表示数字的字符串 数字 NUMBER 数字 $numberInt，其值与 $numberDouble 的值相同
$numberLong，值为 JSON 编号或表示数字的字符串 数字 NUMBER 数字 $numberLong，其值与 $numberDouble 的值相同

$binary，值为以下值之一：

  • 基数为 64 个字符的字符串
  • 字段为 base64 和 subType 的对象，其值分别为基数为 64 个字符的字符串和数字 0（任意二进制）或 4 (UUID)

如果该值是包含 64 个字符的字符串，则扩展对象也可以具有值为 0 或 4 的字段 $subtype，该字段以单字节整数 (0-255) 或 2 个字符的十六进制字符串表示。

二进制 BLOB 或 RAW

string

转换等效于 SQL 函数 rawtohex 的使用。

以下值之一：
  • 值为 base-64 字符的字符串的 $binary
  • $rawid，值为 32 个十六进制字符的字符串，如果输入的 subType 值为 4 (UUID)
$oid，值为 24 个十六进制字符的字符串 二进制 RAW(12)

string

转换等效于 SQL 函数 rawtohex 的使用。

$rawid，值为 24 个十六进制字符的字符串
值为 $rawhex 且具有偶数个十六进制字符的字符串 二进制 RAW

string

转换等效于 SQL 函数 rawtohex 的使用。

值为 base-64 字符的字符串的 $binary，右侧填充了 = 字符
$rawid，值为 24 或 32 个十六进制字符的字符串 二进制 RAW

string

转换等效于 SQL 函数 rawtohex 的使用。

$rawid
值为 ISO 8601 日期字符串的 $oracleDate 日期 DATE string 值为 ISO 8601 日期字符串的 $oracleDate
值为 ISO 8601 时间戳字符串的 $oracleTimestamp 时间戳 TIMESTAMP string 值为 ISO 8601 时间戳字符串的 $oracleTimestamp
$oracleTimestampTZ，值为带数字时区偏移量的 ISO 8601 时间戳字符串，或带有 Z 带时区的时间标记 TIMESTAMP WITH TIME ZONE string $oracleTimestampTZ，值为带数字时区偏移量的 ISO 8601 时间戳字符串，或带有 Z

$date，值为以下值之一：

  • 自 1990 年 1 月 1 日起的整数毫秒计数
  • ISO 8601 时间戳字符串
  • 自 1990 年 1 月 1 日起具有值整数毫秒计数的字段 numberLong 的对象
带时区的时间标记 TIMESTAMP WITH TIME ZONE string $oracleTimestampTZ，值为带数字时区偏移量的 ISO 8601 时间戳字符串，或带有 Z
$intervalDaySecond，值为 ISO 8601 间隔字符串（为 SQL 函数 to_dsinterval 指定） 白天秒间隔 INTERVAL DAY TO SECOND string 为 SQL 函数 to_dsinterval 指定的值为 ISO 8601 间隔字符串的 $intervalDaySecond
为 SQL 函数 to_yminterval 指定的值为 ISO 8601 间隔字符串的 $intervalYearMonth 年月间隔 INTERVAL YEAR TO MONTH string 为 SQL 函数 to_yminterval 指定的值为 ISO 8601 间隔字符串的 $intervalYearMonth

两个字段：

  • 字段 $vector，其值为数组，其元素为数字或字符串 "Nan"、"Inf" 和 "-Inf"（表示非数字和无限值）。

  • 字符串值为 "float32" 或 "float64" 的字段 $vectorElementType。这些数字分别对应于 IEEE 32 位和 IEEE 64 位数字。

向量 VECTOR 数字阵列

两个字段：

  • 字段 $vector，其值为数组，其元素为数字或字符串 "Nan"、"Inf" 和 "-Inf"（表示非数字和无限值）。

  • 字符串值为 "float32" 或 "float64" 的字段 $vectorElementType。

脚注 1 字符串值不区分大小写。例如，"NAN" "nan" 和 "nAn" 被接受且等效，类似地为 "INF"、"inFinity" 和 "iNf"。无限大的（"Infinity" 或 "Inf"）和较小的（"-Infinity" 或 "-Inf"）数字可接受为全字或缩写。

脚注 2 在输出时，仅使用这些字符串值。没有全字 Infinity 或字母大小写变量。

相关内容