DBMS_CLOUD Avro、ORC 和 Parquet 支持

本节介绍自治 AI 数据库随附的 DBMS_CLOUD Avro、ORC 和 Parquet 支持。

Avro、ORC 或 Parquet 的 DBMS_CLOUD 程序包格式选项

DBMS_CLOUD 中的 format 参数指定源文件的格式。

指定格式参数的两种方法为：

format => '{"format_option" : "format_value" }'

和

format => json_object('format_option' value 'format_value')

示例：

format => json_object('type' VALUE 'CSV')

要指定多个格式选项，请使用 "," 分隔值。

例如：

format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')

格式选项	说明	语法
regexuri	如果将 regexuri 的值设置为 TRUE，则可以在云源文件 URI 中的文件名中使用通配符和正则表达式。 当 regexuri 参数设置为 FALSE 时，字符 "*" 和 "?" 被视为通配符。当 regexuri 参数设置为 TRUE 时，字符 "*" 和 "?" 是指定正则表达式模式的一部分。 仅 URI 中的文件名或子文件夹路径支持正则表达式模式，模式匹配与 REGEXP_LIKE 函数执行的模式匹配相同。目录名称不支持正则表达式模式。 对于外部表，仅对象存储中的某个文件上创建的表支持此选项。 例如： format => JSON_OBJECT('regexuri' value TRUE) 有关 REGEXP_LIKE 条件的详细信息，请参阅 Oracle Database 19c SQL Language Reference 或 Oracle Database 26ai SQL Language Reference 中的 REGEXP_LIKE Condition 。	regexuri: True 默认值：False
type	指定文件类型。	type: avro | orc | parquet
schema	将方案设置为 first 或 all 时，外部表列和数据类型会自动从 Avro、ORC 或 Parquet 文件元数据派生。 列名将与 Avro、ORC 或 Parquet 中的列名匹配。数据类型从 Avro、ORC 或 Parquet 数据类型转换为 Oracle 数据类型。所有列都将添加到表中。 值 first 指定使用 file_uri_list 中第一个 Avro、ORC 或 Parquet 文件中的元数据自动生成列及其数据类型。如果所有文件都具有相同的方案，请使用 first。 值 all 指定使用 file_uri_list 中所有 Avro、ORC 或 Parquet 文件的元数据自动生成列及其数据类型。如果文件可能具有不同的方案，请使用 all（较慢）。 默认值：如果指定 column_list，则忽略 schema 值（如果指定）。如果未指定 column_list，则 schema 默认值为 first。 注：对于 Avro、ORC 或 Parquet 格式文件，schema 格式选项不可用，必须使用 DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE 过程为分区的外部表指定 column_list 参数。	schema : first | all

DBMS_CLOUD 程序包 Avro 到 Oracle 数据类型映射

介绍 Avro 数据类型到 Oracle 数据类型的映射。

注：从 Oracle Database 19c 开始，支持复杂类型（例如映射、数组和结构）。有关使用 Avro 复合磁带库类型的信息，请参见 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

Avro 类型	Oracle 类型
INT	NUMBER(10)
LONG	数字 (19)
BOOL	数字 (1)
UTF8 BYTE_ARRAY	RAW(2000)
FL	BINARY_FLOAT
数据库	BINARY_DOUBLE
DECIMAL(p)	NUMBER(p)
DECIMAL(p,s)	NUMBER(p,s)
DATE	DATE
STRING	VARCHAR2
时间（毫米）	VARCHAR2（20 字节）
时间（微）	VARCHAR2（20 字节）
时间戳 _MILLIS	TIMESTAMP(3)
时间戳（微米）	TIMESTAMP(6)
ENUM	VARCHAR2(n)，其中：“n”是 AVRO ENUM 可能值的实际最大长度
DURATION	RAW(2000)
已修复	RAW(2000)
NULL	VARCHAR2(1) 字节

有关使用 Avro 复合磁带库类型的信息，请参见 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

DBMS_CLOUD 程序包 ORC 到 Oracle 数据类型的映射

介绍 ORC 数据类型到 Oracle 数据类型的映射。

有关使用 ORC 复合磁带库类型的信息，请参见 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

ORC 类型	Oracle 类型	更多信息
数组	VARCHAR2(n) JSON 格式	DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型
bigint（64 位）	数字 (19)
二进制	BLOB
boolean（1 位）	数字 (1)
字符	CHAR(n)
日期	DATE
双精度	BINARY_DOUBLE
浮点型	BINARY_FLOAT
int（32 位）	NUMBER(10)
list	VARCHAR2(n) JSON 格式	DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型
地图	VARCHAR2(n) JSON 格式	DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型
smallint（16 位）	数字 (5)
string	VARCHAR2(4000)
struct	VARCHAR2(n) JSON 格式	DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型
时间戳	TIMESTAMP
微薄（8 位）	数字 (3)
工会	VARCHAR2(n) JSON 格式	DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型
varchar	VARCHAR2(n)

DBMS_CLOUD 程序包参数到 Oracle 数据类型的映射

介绍 Parquet 数据类型到 Oracle 数据类型的映射。

注：从 Oracle Database 19c 开始，支持复杂类型（例如映射、数组和结构）。有关使用 Parquet 复杂类型的信息，请参见 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

Parquet 类型	Oracle 类型
UINT_64	编号 (20)
INT_64	数字 (19)
UINT_32	NUMBER(10)
INT_32	NUMBER(10)
UINT_16	数字 (5)
INT_16	数字 (5)
UINT_8	数字 (3)
INT_8	数字 (3)
BOOL	数字 (1)
UTF8 BYTE_ARRAY	VARCHAR2（4000 字节）
FL	BINARY_FLOAT
数据库	BINARY_DOUBLE
DECIMAL(p)	NUMBER(p)
DECIMAL(p,s)	NUMBER(p,s)
DATE	DATE
STRING	VARCHAR2(4000)
时间（毫米）	VARCHAR2（20 字节）
时间 MILLIS_UTC	VARCHAR2（20 字节）
时间（微）	VARCHAR2（20 字节）
TIME_MICROS_UTC	VARCHAR2（20 字节）
时间戳 _MILLIS	TIMESTAMP(3)
时间戳 _MILLIS_UTC	TIMESTAMP(3)
时间戳（微）	TIMESTAMP(6)
时间戳微秒	TIMESTAMP(6)
时间戳（纳诺）	TIMESTAMP(9)

有关使用 Parquet 复杂类型的信息，请参见 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

DBMS_CLOUD 程序包 Oracle 数据类型到参数映射

介绍 Oracle 数据类型到 Parquet 数据类型的映射。

Oracle 类型	Parquet 类型
BINARY_DOUBLE	数据库
BINARY_FLOAT	FL
DATE	DATE
NUMBER(p,s)	DECIMAL(p,s)
NUMBER(p)	DECIMAL(p)
TIMESTAMP(3)	时间戳 _MILLIS
TIMESTAMP(3)	时间戳 _MILLIS_UTC
TIMESTAMP(6)	时间戳（微米）
TIMESTAMP(6)	时间戳微秒
TIMESTAMP(9)	时间戳（纳诺）
VARCHAR2(4000)	STRING

NLS 会话参数

NLS 会话参数 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT 和 NLS_NUMERIC_CHARACTERS 定义在查询具有这些列类型的表时，如何显示具有时区格式的日期、时间戳、时间戳和具有小数标记的时间戳的基数分隔符。

此外，使用 DBMS_CLOUD.EXPORT_DATA 导出数据并指定 Parquet 输出时，自治 AI 数据库将从 NLS_SESSION_PARAMETERS 表中读取这些参数的值。自治 AI 数据库使用这些值将 Oracle 数据类型 DATE 或 TIMESTAMP 转换为 Parquet 类型。

NLS_SESSION_PARAMETERS 参数支持 RR 格式掩码（两个字符年份指定）。

使用 DBMS_CLOUD.EXPORT_DATA 将数据导出到 Parquet 时，这些参数不支持年份的 RR 格式掩码。如果尝试导出为 parquet 并且 NLS_SESSION_PARAMETERS 设置为使用 RR 格式掩码（RR 格式的默认值取决于 NLS_TERRITORY 参数的值），则会引发应用程序错误。

当某个参数 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT 或 NLS_TIMESTAMP_TZ_FORMAT 使用 RR 格式掩码时，必须将格式值更改为支持的值，才能使用 DBMS_CLOUD.EXPORT_DATA 将数据导出到 Parquet。例如：

ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
ALTER SESSION SET  NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM';

更改值后，可以通过查询 NLS_SESSION_PARAMETERS 视图来验证更改：

SELECT value FROM NLS_SESSION_PARAMETERS
       WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');

如果设置了 NLS_DATE_FORMAT，则将其应用于数据类型为 DATE 的列。如果设置了 NLS_TIMESTAMP_FORMAT，则将其应用于数据类型为 TIMESTAMP 的列。如果设置了 NLS_TIMESTAMP_TZ_FORMAT，则将其应用于数据类型为 TIMESTAMP WITH TIME ZONE 的列。

有关详细信息，请参阅 Oracle Database 19c Database Globalization Support Guide 或 Oracle Database 26ai Database Globalization Support Guide 中的 Date and Time Parameters ，以及 Oracle Database 19c Database Globalization Support Guide 或 Oracle Database 26ai Database Globalization Support Guide 中的 NLS Data Dictionary Views 。

DBMS_CLOUD 程序包 Avro、ORC 和 Parquet Complex 类型

介绍 Avro、ORC 和 Parquet 复杂数据类型到 Oracle 数据类型的映射。

自治 AI 数据库支持复杂数据类型，包括以下复杂类型：

• struct
• list
• 地图
• 工会
• 数组

当指定源文件类型为 Avro、ORC 或 Parquet 并且源文件包含复杂列时，自治 AI 数据库查询将返回复杂列的 JSON。这简化了查询结果的处理；您可以在各种文件类型和数据类型中一致地使用 Oracle 强大的 JSON 解析功能。下表显示了自治 AI 数据库中复杂类型的格式：

注：复杂字段映射到 VARCHAR2 列，并应用 VARCHAR2 大小限制。

类型	Parquet	ORC	Avro	Oracle
列表：值列表	列出	列出	Array	VARCHAR2（JSON 格式）
映射：具有单个键的对象列表	地图	地图	地图	VARCHAR2（JSON 格式）
Union：不同类型的值	不可用	并集	并集	VARCHAR2（JSON 格式）
对象：零个或多个键 - 值对	结构	结构	记录	VARCHAR2（JSON 格式）

如果 ORC、Parquet 或 Avro 源文件包含复杂类型，则可以查询这些常见复杂类型的 JSON 输出。例如，以下内容显示了具有复杂类型的 ORC 文件 movie-info.orc（对 Parquet 和 Avro 源文件应用相同的复杂类型处理）。

考虑具有以下方案的 movie-info.orc 文件：

id    int
original_title string
overview       string
poster_path    string
release_date   string
vote_count     int
runtime        int
popularity     double
genres         array<struct<id:int,name:string>

请注意，每部影片都使用 genres 数组按多个 genres 进行分类。genres 数组是 structs 的 array，每个项目都有一个 id (int) 和一个 name (string)。genres 数组被视为复杂类型。可以使用 DBMS_CLOUD.CREATE_EXTERNAL_TABLE 在此 ORC 文件上创建表，如下所示：

BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
        table_name =>'movie_info',
        credential_name =>'OBJ_STORE_CRED',
        file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc',
        format => '{"type":"orc", "schema": "first"}');
END;
/

创建外部表时，数据库会根据 ORC 文件中的方案自动生成列（如果使用 Avro 或 Parquet，同样适用）。对于此示例，DBMS_CLOUD.CREATE_EXTERNAL_TABLE 将在数据库中创建一个表，如下所示：

CREATE TABLE "ADMIN"."MOVIE_INFO"
    ( "ID"
      NUMBER(10,0),
      "ORIGINAL_TITLE"  VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "OVERVIEW"        VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "POSTER_PATH"     VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "RELEASE_DATE"    VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "VOTE_COUNT"      NUMBER(10,0),
      "RUNTIME"         NUMBER(10,0),
      "POPULARITY"      BINARY_DOUBLE,
      "GENRES"          VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP"
    )  DEFAULT COLLATION "USING_NLS_COMP"
    ORGANIZATION EXTERNAL
     ( TYPE      ORACLE_BIGDATA
       DEFAULT DIRECTORY "DATA_PUMP_DIR"
       ACCESS PARAMETERS
       ( com.oracle.bigdata.credential.name=OBJ_STORE_CRED
         com.oracle.bigdata.fileformat=ORC
   )
       LOCATION
        (
      'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc'
           )
     )
   REJECT LIMIT UNLIMITED
   PARALLEL;
 )

现在，您可以查询电影数据：

SELECT original_title, release_date, genres
     FROM movie_info
     WHERE release_date > '2000'
     ORDER BY original_title;

这将生成以下输出：

original_title              release_date   genres
(500) Days of Summer        2009           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":19,"name":"Western"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC                   2008           [{"id":6,"name":"Comedy"}]
11:14                       2003           [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours                   2010           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30              2004           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
1408                        2007           [{"id":45,"name":"Sci-Fi"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":6,"name":"Comedy"},{"id":18,"name":"War"}]

请注意，复数类型 genres 作为 JSON 数组返回。

为了使 JSON 数据更有用，您可以使用 Oracle 的 JSON 函数转换列。例如，您可以使用 JSON "." 表示法以及更强大的转换函数，例如 JSON_TABLE。

有关“。”表示法的信息，请参阅 Oracle Database 19c JSON Developer’s Guide 或 Oracle Database 26ai JSON Developer’s Guide 中的 Simple Dot-Notation Access to JSON Data 。

有关 JSON_TABLE 的信息，请参阅 Oracle Database 19c JSON Developer’s Guide 或 Oracle Database 26ai JSON Developer’s Guide 中的 SQL/JSON Function JSON_TABLE 。

以下示例显示了对表的查询，该查询接受数组的每个值并将值转换为结果集中的一行：

SELECT original_title, release_date, m.genre_name, genres
    FROM movie_info mi,
       JSON_TABLE(mi.genres, '$.name[*]'
        COLUMNS (genre_name VARCHAR2(25) PATH
      '$')
                 ) AS m
 WHERE rownum < 10;

JSON_TABLE 为数组的每个值创建一个行，考虑外部联接，并对结构进行语法分析以提取类型名称。这将生成以下输出：

original_title                   release_date         genre_name        genres
(500) Days of Summer             2009                 Drama             [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Comedy            [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Horror            [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Western           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 War               [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Romance           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC                        2008                 Comedy            [{"id":6,"name":"Comedy"}]
11:14                            2003                 Family            [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
11:14                            2003                 Thriller          [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours                        2010                 Comedy            [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
127 Hours                        2010                 Drama             [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30                   2004                 Romance           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 Comedy            [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 War               [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 Drama             [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]

DBMS_CLOUD 程序包 Avro、ORC 和 Parquet 到 Oracle 列名的映射

介绍有关如何将 Avro、ORC 和 Parquet 列名转换为 Oracle 列名的规则。

Avro、ORC 和 Parquet 列名支持以下内容，但可能需要对外部表中的 Oracle SQL 引用使用双引号。因此，为了便于使用且避免在引用列名时使用双引号，如果可能，请不要在 Avro、ORC 和 Parquet 列名中使用以下内容：

• 嵌入式空白
• 前导编号
• 前导下划线
• Oracle SQL 保留字

下表显示了各种类型的 Avro、ORC 和 Parquet 列名，以及用于在外部表中的 Oracle 列名中使用列名的规则。

Avro、ORC 或 Parquet 名称	CREATE TABLE 名称	Oracle 目录	有效 SQL	说明
部件、部件或部件	部件、部件、部件	部件	select part select Part select paRt select PART	Oracle 会隐式使用大写、不带引号的列名。
订单编号	“订单编号”	订单编号	select "Ord No"	当存在嵌入的空格时，需要使用双引号，这也会保留字符大小写。
__index_key__	“__index_key__”	__index_key__	select "__index_key__"	如果存在前导下划线，则需要使用双引号，这也会保留字符大小写。
6Way	“6 种方式”	6Way	select "6Way"	如果存在前导数字位，则需要使用双引号，这也会保留字符大小写。
CREATE、CREATE 或 CREATE 等。（任何情况变化）分区、分区、分区等（对于 Oracle 保留字）	“创建”“分区”	创建分段	select "CREATE" select "PARTITION"	Oracle SQL 保留字周围需要双引号。这些值强制为大写，但在 SQL 中的任何位置使用时，必须始终使用双引号。
ROWID、ROWID、ROWID 等（对于 ROWID，请参见注释）	行标识		select "rowid" select "Rowid" select "ROWid" select "rowid"	对于 ROWID，ROWID 的任何混合或小写变体都会保留大小写，并且必须始终加双引号并使用原始大小写变体。由于与表的 Oracle ROWID 存在固有冲突，如果指定大写 ROWID，则它会自动存储为小写“行 ID”，在引用时必须始终加双引号。

注

• 通常，可以在不带双引号的情况下引用外部表中的列名。

• 除非列名中嵌入了空格、前导下划线 ("_") 或前导数字位（"0" 到 "9"），否则列名的原始大小写将保留，并且必须始终用双引号引用列名，并使用 Avro、ORC 或 Parquet 列名的原始大小写（大写、小写或混合大小写）。

• 使用 DBMS_CLOUD.CREATE_EXTERNAL_TABLE 以指定为 avro、orc 或 parquet 的格式创建外部表后，在 SQL*Plus 中使用 DESCRIBE 命令查看表的列名。

• 在 Avro、ORC 或 Parquet 列名中使用 Oracle SQL Reserved Word 时，在 SQL 中的任何位置引用时，它们必须始终为双引号。有关详细信息，请参阅 Oracle Database 26ai SQL Language Reference 中的 Oracle SQL Reserved Words 或 Oracle Database 26ai SQL Language Reference 。