从云中的文件加载数据

PL/SQL 程序包 DBMS_CLOUD 支持将数据从云中的文件加载到在 Autonomous Database on Dedicated Exadata Infrastructure 中创建的表中。

可以使用 DBMS_CLOUD 提供的以下 PL/SQL 过程加载不同文件格式的数据:
  • 使用 DBMS_CLOUD.COPY_DATA 过程的云中的文本文件
  • 云中的 JSON 文件,使用 DBMS_CLOUD.COPY_TEXT 过程
在从文件中加载数据之前,请确保:
  • 源文件可用作客户端计算机中的本地文件,也可以上载到基于云的对象存储(例如 Oracle Cloud Infrastructure Object Storage),并且可供尝试加载数据的数据库用户访问。
  • 您的云对象存储身份证明使用 DBMS_CLOUD.CREATE_CREDENTIAL 过程进行存储。有关更多信息,请参见 Create Credentials

程序包 DBMS_CLOUD 支持从以下云服务中的文件进行加载:Oracle Cloud Infrastructure Object StorageOracle Cloud Infrastructure Object Storage ClassicAzure Blob StorageAmazon 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;
/

usernamepassword 提供的值取决于您使用的云对象存储服务:

  • Oracle Cloud Infrastructure Object Storageusername 是您的 Oracle Cloud Infrastructure 用户名,password 是您的 Oracle Cloud Infrastructure 验证令牌。请参见 Working with Auth Tokens

  • Oracle Cloud Infrastructure Object Storage Classicusername 是您的 Oracle Cloud Infrastructure Classic 用户名,password 是您的 Oracle Cloud Infrastructure Classic 密码。

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

从文本文件加载数据

了解如何使用 DBMS_CLOUD.COPY_DATA 过程将数据从云中的文本文件加载到 Autonomous Database

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

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
  1. 使用 DBMS_CREDENTIAL.CREATE_CREDENTIAL 过程存储云对象存储身份证明。有关详细信息,请参阅创建身份证明
  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:定义用于描述源文件格式的指定选项。有关可以指定的格式选项的信息,请参阅格式参数

    有关详细信息,请参阅 COPY_DATA 过程

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

了解如何使用 DBMS_CLOUD.COPY_DATA 过程将带分隔符的文档的 JSON 文件加载到 Autonomous Database 的集合中。

此示例从以行分隔的文件加载 JSON 值并使用 JSON 文件 myCollection.json。每个值(每行)都作为单个文档加载到 Autonomous Database 上的一个集合中。

以下是此类文件的示例。它有三行,每行一个对象。其中每个对象都作为单独的 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 。指定的任何其他格式都将导致错误。有关可以指定的格式选项的信息,请参阅格式参数

    有关详细信息,请参阅 COPY_COLLECTION 过程

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

了解如何使用 DBMS_CLOUD.COPY_COLLECTION 过程将 JSON 文档数组加载到 Autonomous Database 的集合中。

此示例使用 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 。指定的任何其他格式都将导致错误。有关可以指定的格式选项的信息,请参阅格式参数

    使用格式选项 unpackarraysfruit_array.json 装入 DBMS_CLOUD.COPY_COLLECTION 可使该过程识别源中的数组值。因此,不像默认情况下那样将数据作为单个文档加载,而是将数据作为单个文档加载到集合 fruits 中,数组中的每个值都作为单个文档加载。

    有关详细信息,请参阅 COPY_COLLECTION 过程

将 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_DATAtypejson。指定其他格式值以定义用于描述 JSON 源文件格式的选项。有关更多信息,请参见 DBMS_CLOUD Package Format Options

    在此示例中,namespace-string 是 Oracle Cloud Infrastructure 对象存储名称空间,bucketname 是存储桶名称。有关更多信息,请参见 Understanding Object Storage Namespaces

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

监视和排除数据加载故障

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

  • 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_TABLEBADFILE_TABLE 表将存储两天,然后自动删除。

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

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

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

从包含此类扩展对象的文本 JSON 数据创建本机二进制 JSON 数据时,可以选择将其替换为对应(本机二进制)JSON 标量值。

扩展对象的一个示例是 {"$numberDecimal":31}。It represents a JSON scalar value of the nonstandard type decimal number, and when interpreted as such it is replaced by a decimal number in native binary format.

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

相反,当您使用 Oracle SQL 函数 json_serialize 将二进制 JSON 数据序列化为文本 JSON 数据(VARCHAR2CLOBBLOB)时,您可以使用关键字 EXTENDED 将(本机二进制)JSON 标量值替换为相应的文本扩展 JSON 对象。

注意:

如果您使用的数据库是 Oracle Autonomous Database,则可以使用 PL/SQL 过程 DBMS_CLOUD.copy_collection 从 JSON 数据文件(例如公用 NoSQL 数据库(包括 Oracle NoSQL Database)生成的数据)创建 JSON 文档集合。

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

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

  • Exchange (导入/导出):

    • 摄取包含扩展对象的现有 JSON 数据(从某个位置)。

    • 使用扩展对象将本机二进制 JSON 数据序列化为文本 JSON 数据,以便在数据库外部使用。

  • 检查本机二进制 JSON 数据:通过查看相应的扩展对象来查看您拥有的内容。

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

提示:

作为检查示例,请考虑将 {"dob" : "2000-01-02T00:00:00"} 等对象作为序列化本机 JSON 数据的结果。"2000-01-02T00:00:00" 是对类型为 date 的本机二进制值进行串行化的结果,还是本机二进制值只是一个字符串?将 json_serialize 与关键字 EXTENDED 一起使用可让您知道。

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

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

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

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

表 - 扩展的 JSON 对象类型关系

扩展对象类型(输入) 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 的值相同
值为带符号的 32 位整数或表示数字的字符串的 $numberInt 数字 NUMBER

数字

$numberInt 的值与 $numberDouble 的值相同
值为 JSON 编号或表示该编号的字符串的 $numberLong 数字 NUMBER

数字

$numberLong 的值与 $numberDouble 的值相同

具有以下值之一的 $binary

  • base-64 字符的字符串
  • 具有字段 base64subType 的对象,其值分别是 base-64 字符和 number 0(任意二进制)或 4 (UUID) 的字符串

当值是 base-64 字符的字符串时,扩展对象还可以具有值为 0 或 4 的字段 $subtype,该字段表示为单字节整数 (0-255) 或 2 个字符的十六进制字符串。表示此类整数

二进制 BLOBRAW

string

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

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

string

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

值为 24 个十六进制字符的字符串的 $rawid
值为带偶数十六进制字符的字符串的 $rawhex 二进制 RAW

string

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

值为 64 个字符的字符串的 $binary,用右填充 = 字符
值为 24 或 32 个十六进制字符的字符串的 $rawid 二进制 RAW

string

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

$rawid
值为 ISO 8601 日期字符串的 $oracleDate 日期 DATE

string

值为 ISO 8601 日期字符串的 $oracleDate
值为 ISO 8601 时间戳字符串的 $oracleTimestamp 时间戳 TIMESTAMP

string

值为 ISO 8601 时间戳字符串的 $oracleTimestamp
值为 ISO 8601 时间戳字符串且具有数字时区偏移量或 Z$oracleTimestampTZ 带有时区的时间戳 TIMESTAMP WITH TIME ZONE

string

值为 ISO 8601 时间戳字符串且具有数字时区偏移量或 Z$oracleTimestampTZ

$date,值为以下值之一:

  • 自 1990 年 1 月 1 日以来的整数毫秒计数
  • ISO 8601 时间戳字符串
  • 从 1990 年 1 月 1 日起,字段为 numberLong 且值为整数毫秒计数的对象
带有时区的时间戳 TIMESTAMP WITH TIME ZONE

string

值为 ISO 8601 时间戳字符串且具有数字时区偏移量或 Z$oracleTimestampTZ
具有为 SQL 函数 to_dsinterval 指定的 ISO 8601 间隔字符串值的 $intervalDaySecond daysecondInterval INTERVAL DAY TO SECOND

string

具有为 SQL 函数 to_dsinterval 指定的 ISO 8601 间隔字符串值的 $intervalDaySecond
具有为 SQL 函数 to_yminterval 指定的 ISO 8601 间隔字符串值的 $intervalYearMonth yearmonthInterval 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 或字母大小写变体。