从云中的文件加载数据
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 Storage 、Oracle Cloud Infrastructure Object Storage Classic 、Azure Blob Storage 和 Amazon S3 。
创建身份证明
了解如何使用 DBMS_CLOUD.CREATE_CREDENTIAL
过程存储云对象存储身份证明。
DBMS_CLOUD.CREATE_CREDENTIAL
过程。例如:BEGIN
DBMS_CLOUD.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.COPY_DATA
过程将数据从云中的文本文件加载到 Autonomous Database 。
此示例中的源文件 channels.txt
具有以下数据:
S,Direct Sales,Direct T,Tele Sales,Direct C,Catalog,Indirect I,Internet,Indirect P,Partners,Others
将分隔文档的 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 }
将 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 }]
监视和排除数据加载故障
使用 PL/SQL 程序包
执行的所有数据加载操作都记录在表 DBMS_CLOUD
dba_load_operations
和 user_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_TABLE
和 BADFILE_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 数据(VARCHAR2
、CLOB
或 BLOB
)时,您可以使用关键字 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) 指定关键字 EXTENDED
时 json_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 的值相同 |
具有以下值之一的
当值是 base-64 字符的字符串时,扩展对象还可以具有值为 0 或 4 的字段 |
二进制 | BLOB 或 RAW |
string 转换等效于 SQL 函数 |
以下值之一:
|
值为 24 个十六进制字符的字符串的 $oid
|
二进制 | RAW(12) |
string 转换等效于 SQL 函数 |
值为 24 个十六进制字符的字符串的 $rawid
|
值为带偶数十六进制字符的字符串的 $rawhex
|
二进制 | RAW |
string 转换等效于 SQL 函数 |
值为 64 个字符的字符串的 $binary ,用右填充 = 字符
|
值为 24 或 32 个十六进制字符的字符串的 $rawid
|
二进制 | RAW |
string 转换等效于 SQL 函数 |
$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 |
|
带有时区的时间戳 | 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 |
数字数组 |
两个字段:
|
脚注 1 字符串值不区分大小写。例如,接受 "NAN"
"nan"
和 "nAn"
,并且与 "INF"
、"inFinity"
和 "iNf"
类似。接受无限大("Infinity"
或 "Inf"
)和较小("-Infinity"
或 "-Inf"
)的数字,并带有完整单词或缩写。
脚注 2 在输出上,仅使用这些字符串值 - 不使用全词 Infinity 或字母大小写变体。
另请参阅: